Jean stillede denne uges Excel-spørgsmål:
Jeg prøver at konvertere en leverandørprisbog i Excel for at matche deres UPC-koder til vores UPC-kodesystem. Deres prisbog har en kolonne i Excel med en række numre f.eks. 000004560007 ELLER cel000612004. Hvad jeg skal gøre er to gange. Jeg skal fjerne de første tre nuller uden at slette andre 0'er i cellen. Derefter skal jeg tilføje en 3-cifret kode "upc" før det første numeriske ciffer i cellen. Dette vil være et løbende behov. Jeg bliver nødt til at træne flere mennesker til at bruge Excel-systemet.
Det lyder som om Jean allerede har overvejet at bruge Edit-Replace for at slippe af med de tre nuller. Dette fungerer ikke, fordi udførelse af en redigering af "000004560007" ville få begge forekomster af 000 til at forsvinde.
Først vil jeg foreslå, at Jean indsætter en midlertidig kolonne ved siden af de aktuelle priskoder, skriver en formel for at udføre transformationen og derefter bruger Edit-Copy, Edit-PasteSpecial-Values til at kopiere formlen tilbage over de originale priskoder.
Den dårligt dokumenterede REPLACE () -funktion gør tricket i dette tilfælde. Jeg var skuffet over implementeringen af REPLACE (). Jeg ville have troet, at det ville bede om, at en bestemt tekst skulle erstattes, men i stedet beder den om, at brugeren finder ud af tegnpositioner, der skal erstattes. REPLACE erstatter en del af en tekststreng med en ny streng. De fire argumenter, du sender til funktionen, er cellen, der indeholder den gamle tekst, placeringen af tegnet i den gamle tekst, du vil erstatte, antallet af tegn, der skal erstattes, og den nye tekst, der skal bruges.
Min forenkling antagelse er, at de tre nuller repræsenterer starten på UPC-koden i strengen. Der er således ikke behov for at søge efter det første numeriske tegn i cellen. Når formlen har fundet de tre nuller, kan den erstatte de tre nuller med strengen "upc".
For at finde placeringen af den første forekomst af "000" i teksten, skal du bruge denne formel:
=FIND(A2,"000")
Formlen, som Jean ville have brug for at indtaste i celle B2, ville være:
=REPLACE(A2,FIND("000",A2),3,"upc")
Min tanke er at få prisanalytikerne til at fremhæve en række celler og derefter påberåbe sig denne makro. Makroen bruger en sløjfe med "For hver celle i markering" i begyndelsen. Med denne sløjfe bliver "celle" en speciel rækkevariabel. Du kan bruge cell.address eller cell.value eller cell.row til at finde adressen, værdien eller rækken for den aktuelle celle i sløjfen. Her er makroen:
Public Sub Jean() ' This macro will replace the first occurrence of "000" ' in each selected cell with the string "upc" ' copyright 1999 www.MrExcel.com For Each cell In Selection cell.Value = Replace(cell.Value, "000", "upc", 1, 1, vbTextCompare) Next cell End SubBemærk at dette er en destruktiv makro. Når brugeren fremhæver en række celler og kører makroen, ændres disse celler. Det siger sig selv, at du vil teste din makro grundigt på testdata, før du sætter den løs på ægte produktionsdata. Når du har en situation som Jean, hvor du konstant bliver nødt til at omdanne en kolonne ved hjælp af en kompleks formel, kan det være et effektivt og tidsbesparende værktøj at skrive en simpel makro som den her illustreret.
