Avatar billede rsorgenfri Novice
29. september 2014 - 09:41 Der er 13 kommentarer og
1 løsning

Ændre et kundenummer på mere end 10 cifre til bogstav + 9 cifre

Jeg skal ændre hele min kundedatabase til implementering af nyt økonomisystem. Mit gamle kunne håndtere ubegrænset antal cifre, men det nye kan "kun" 10. Jeg er ved at lave konverteringer til det nye system, man kan ikke finde ud at at lave de lange kundenumre om til et bogstav + 9 cifre som det nye kan. Det drejer sig om ca. 3000 kundenumre, så det ville være rart hvis en kan hjælpe mig.

Formlen skal gå ud fra flg.:
Hvis der er mere end 10 cifre i cellen skal den indsætte flg. bogstav ved ciffer begyndelse: (Jeg har kundenumre der starter med 10 som er under 10 cifre og de skal blot bibeholdes og derfor kan jeg ikke bare gøre de andre kortere).

F.eks.
10123456789 skal blive til Q123456789
30123456789 skal blive til R123456789
osv.

Jeg har i mit system lavet flg. som det skal starte med:
ved start med:
10 (+9cifre) = Q (+9cifre)
30 (+9cifre) = R (+9cifre)
31 (+9cifre) = A (+9cifre)
32 (+9cifre) = B (+9cifre)
33 (+9cifre) = C (+9cifre)
34 (+9cifre) = D (+9cifre)
35 (+9cifre) = E (+9cifre)
36 (+9cifre) = F (+9cifre)
37 (+9cifre) = G (+9cifre)
38 (+9cifre) = H (+9cifre)
39 (+9cifre) = I (+9cifre)
40 (+9cifre) = J (+9cifre)
41 (+9cifre) = K (+9cifre)
42 (+9cifre) = L (+9cifre)
43 (+9cifre) = M (+9cifre)
44 (+9cifre) = N (+9cifre)
45 (+9cifre) = O (+9cifre)
46 (+9cifre) = P (+9cifre)

Jeg bliver desværre nødt til at kende det gamle kundenummer mht. historik, så derfor bliver det nødt til at være så kringlet.

Håber i kan hjælpe mig der ude :-)
29. september 2014 - 10:38 #1
Er der kundenume med mere end 11 cifre?
Avatar billede rsorgenfri Novice
29. september 2014 - 10:44 #2
Nej, 11 cifre er max. Jeg har en række med 8 cifre som skal blive som de er og så er der dem med 11 cifre der skal være 10 med den bogstavkombination som beskrevet.
Avatar billede xl-Enthusiast Ekspert
29. september 2014 - 10:47 #3
Med gamle kundenumre i A1:A(n) og med:

10    Q
30    R
31    A
32    B
33    C
34    D
35    E
36    F
37    G
38    H
39    I
40    J
41    K
42    L
43    M
44    N
45    O
46    P

i D1:E18 prøv følgende:

=HVIS(LÆNGDE(A1)<10;A1;INDEKS($E$1:$E$18;SAMMENLIGN(--VENSTRE(A1;2);$D$1:$D$18;0))&MIDT(A1;3;100))
29. september 2014 - 10:47 #4
... hvis ikke:

Opret en liste med de 2 første tegn, der skal konverteres, og de værdier de skal konverteres til:
10 Q
30 R
31 A
osv.

Navngiv listen "opslag"
Indtast denne formel ud for det føste kundenummer (A1 rette 3 steder til referencen til kundenummeret), og kopier den til alle rækker:
=HVIS(LÆNGDE(A1)>10;LOPSLAG(VENSTRE(A1;2);opslag;2)&HØJRE(A1;9);A1)
Avatar billede rsorgenfri Novice
29. september 2014 - 12:32 #5
Hej igen

Mange tak. Det virker som en automatisk formel.
Jeg ville helst hvis den kunne lave udregningen i en formel.

Jeg har prøvet med denne formel, men den regner det ikke ud i et step som jeg gerne ville have. Kan den bruges på nogen måde hvis den bliver omskrevet lidt?

=IF(LEN(A3)=11;(REPLACE("10";1;2;"Q")&RIGHT(A3;9));(REPLACE("30";1;2;"R")&RIGHT(A3;9)))

den virker ikke som den ser ud.
29. september 2014 - 13:14 #6
Dette er sort snak:
"Det virker som en automatisk formel.
Jeg ville helst hvis den kunne lave udregningen i en formel."

Hvad mener du med det???

Hvis det du ønsker er at have de nye værdier i stadet for de gamle, kan du kopiere de nye og indsætte dem som værdier.
Avatar billede rsorgenfri Novice
29. september 2014 - 13:30 #7
Undskyld, jeg mente at det skulle være i et step i stedet for over flere kolonner. Den formel jeg har lavet:

=IF(LEN(A3)=11;(REPLACE("10";1;2;"Q")&RIGHT(A3;9));(REPLACE("30";1;2;"R")&RIGHT(A3;9)))

Virker så den tager kundenummeret 10123456789 og laver direkte om til Q123456789.

Problemet er at den kun læser første del af formlen. Dvs. at 2. del ;(REPLACE("30";1;2;"R")&RIGHT(A3;9) bliver udeladt. Så hvis jeg indsætter knd.nr. 30123456789, så kommer den igen med Q123456789 i stedet for R123456789

Kan jeg ikke lave formlen så jeg slipper for arket "OPSLAG" som du foreslog.

Jeg ville gerne have det hele i en formel som bliver lang, men så jeg slipper for et ekstra ark eller ekstra kolonner.

Jeg håber det giver mere mening?
Avatar billede xl-Enthusiast Ekspert
29. september 2014 - 13:37 #8
Sig mig, læser du ikke de løsningsforslag du modtager, jeg tænker især på #4?
Avatar billede rsorgenfri Novice
29. september 2014 - 13:52 #9
Hej xl-Enthusiast

Jo jeg læser og den virker også (efter oversættelse): =IF(LEN(A2)<10;A2;INDEX($E$2:$E$19;MATCH(--LEFT(A2;2);$D$2:$D$19))&MID(A2;3;100))

Det er den formel der virker bedst indtil videre.

Men her skal jeg også have mellemregningen. Jeg ville helst hvis jeg unlod arket "OPSLAG" med en kolonne med tal og en kolonne med bogstav. Jeg ville gerne have en kolonne med gammelt kundenummer og en kolonne med en formel der direkte laver nyt kundenummer hvis det kan lade sig gøre.

Det er derfor jeg prøver at lave en formel med en masse "hvis'er" som bare ikke virker:-). Den er slet ikke komplet. Det vil jeg kunne gøre når nr. 2 "IF" virker. Så skal den jo bare laves derud af.
29. september 2014 - 13:57 #10
Uanset hvad kan du ikke undgå en eller anden form for indtastning et andet sted i regnearket; men alt denne ekstra indtastning er jo kun et step på vejen.
Som tidligere beskrevet skal de nye værdier afslutningsvis indsættes hvor de eksisterende står. Herefter kan de midlertidige data slettes igen.
MEN du slipper ikke for dette ekstra trin medmindre du ønsker at forsøge dig med en VBA-løsning, men du skriver jo netop at du ønsker en formelløsning..
29. september 2014 - 13:59 #11
Den letteste løsning er stadig denne (oversat til engelsk):
=IF(LEN(A1)>10;VLOOKUP(LEFT(A1;2);opslag;2)&RIGHT(A1;9);A1)
Avatar billede xl-Enthusiast Ekspert
29. september 2014 - 14:10 #12
=IF(LEN(A1)<10;A1;LOOKUP(--LEFT(A1;2);{10;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46};{"Q";"R";"A";"B";"C";"D";"E";"F";"G";"H";"I";"J";"K";"L";"M";"N";"O";"P"})&MID(A1;3;100))
Avatar billede rsorgenfri Novice
30. september 2014 - 08:28 #13
Hej XL

Det var lige den formel jeg manglede. Det virker perfekt. Mange tak til jer begge for at hjælpe med det her. Det har sparet mig for mange timer på google :-).

God dag
Avatar billede xl-Enthusiast Ekspert
14. oktober 2014 - 09:31 #14
OK.
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester