Jean ställde veckans Excel-fråga:
Jag försöker konvertera en leverantörsprisbok i Excel för att matcha deras UPC-koder till vårt UPC-kodsystem. Deras prisbok har en kolumn i Excel med en serie siffror t.ex. 000004560007 ELLER cel000612004. Vad jag måste göra är tvåfaldigt. Jag måste ta ut de tre första nollorna utan att ta bort några andra nollor i cellen. Därefter måste jag lägga till en tresiffrig kod "upc" före den första siffran i cellen. Detta kommer att vara ett ständigt behov. Jag måste träna flera personer att använda Excel-systemet.
Det låter som att Jean redan övervägde att använda Edit-Replace för att bli av med de tre nollorna. Detta fungerar inte eftersom att göra en redigering ersätter "000004560007" skulle orsaka båda förekomsten av 000 att försvinna.
Först vill jag föreslå att Jean infogar en tillfällig kolumn bredvid de aktuella priskoderna, skriver en formel för att göra omvandlingen och använder sedan Edit-Copy, Edit-PasteSpecial-Values för att kopiera formeln tillbaka över de ursprungliga priskoderna.
Den dåligt dokumenterade REPLACE () -funktionen gör tricket i det här fallet. Jag blev besviken över implementeringen av REPLACE (). Jag skulle ha trott att det skulle be om att en specifik text skulle ersättas, men istället ber den användaren att räkna ut teckenpositioner som ska ersättas. REPLACE ersätter en del av en textsträng med en ny sträng. De fyra argumenten som du skickar till funktionen är cellen som innehåller den gamla texten, positionen för tecknet i den gamla texten du vill ersätta, antalet tecken som ska ersättas och den nya texten som ska användas.
Mitt förenklade antagande är att de tre nollorna representerar uppkomsten av UPC-koden i strängen. Det finns alltså inget behov av att söka efter det första numeriska tecknet i cellen. När formeln har hittat de tre nollorna kan den ersätta dessa tre nollor med strängen "upc".
För att hitta platsen för den första förekomsten av "000" i texten använder du denna formel:
=FIND(A2,"000")
Formeln som Jean skulle behöva ange i cell B2 skulle vara:
=REPLACE(A2,FIND("000",A2),3,"upc")
Min tanke är att prissättningsanalytikerna ska markera ett antal celler och sedan åberopa detta makro. Makrot använder en slinga med "För varje cell i urval" i början. Med denna slinga blir "cell" en speciell intervallvariabel. Du kan använda cell.address eller cell.value eller cell.row för att hitta adressen, värdet eller raden för den aktuella cellen i slingan. Här är makrot:
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 SubObservera att detta är ett destruktivt makro. När användaren markerar ett cellområde och kör makrot ändras dessa celler. Det säger sig självt att du vill testa ditt makro noggrant på testdata innan du släpper loss det på verklig produktionsdata. När du har en situation som Jean där du ständigt kommer att behöva omvandla en kolumn med hjälp av en komplex formel, kan det vara ett effektivt och tidsbesparande verktyg att skriva ett enkelt makro som det som illustreras här.
