Jean stelde de Excel-vraag van deze week:
Ik probeer een leveranciersprijslijst in Excel te converteren om hun UPC-codes af te stemmen op ons UPC-codesysteem. Hun prijslijst heeft een kolom in Excel met een reeks getallen, bijv. 000004560007 OF cel000612004. Wat ik moet doen is tweeledig. Ik moet de eerste drie nullen eruit halen zonder andere nullen in de cel te verwijderen. Vervolgens moet ik een 3-cijferige code "upc" toevoegen voor het eerste numerieke cijfer in de cel. Dit zal een voortdurende behoefte zijn. Ik zal meerdere mensen moeten opleiden om het Excel-systeem te gebruiken.
Het lijkt erop dat Jean al overwoog om Bewerken-Vervangen te gebruiken om de drie nullen te verwijderen. Dit zou niet werken omdat het vervangen van een bewerking op "000004560007" ervoor zou zorgen dat beide exemplaren van 000 zouden verdwijnen.
Eerst wil ik voorstellen dat Jean een tijdelijke kolom invoegt naast de huidige prijscodes, een formule schrijft om de transformatie uit te voeren, en vervolgens Bewerken-Kopiëren, Bewerken-Plakken Speciale waarden gebruikt om de formule terug te kopiëren over de oorspronkelijke prijscodes.
De slecht gedocumenteerde REPLACE () -functie zal in dit geval de slag slaan. Ik was teleurgesteld over de implementatie van REPLACE (). Ik had gedacht dat het zou vragen om een specifieke tekst om te vervangen, maar in plaats daarvan wordt de gebruiker gevraagd de tekenposities te vinden die moeten worden vervangen. REPLACE zal een deel van een tekstreeks vervangen door een nieuwe tekenreeks. De vier argumenten die u aan de functie doorgeeft, zijn de cel met de oude tekst, de positie van het teken in de oude tekst dat u wilt vervangen, het aantal tekens dat moet worden vervangen en de nieuwe tekst die moet worden gebruikt.
Mijn vereenvoudigende aanname is dat de drie nullen het begin van de UPC-code in de string vertegenwoordigen. Het is dus niet nodig om naar het eerste numerieke teken in de cel te zoeken. Zodra de formule de drie nullen heeft gevonden, kan het die drie nullen vervangen door de tekenreeks "upc".
Om de locatie van het eerste exemplaar van "000" in de tekst te vinden, zou u deze formule gebruiken:
=FIND(A2,"000")
De formule die Jean zou moeten invoeren in cel B2 zou zijn:
=REPLACE(A2,FIND("000",A2),3,"upc")
Mijn gedachte is om de prijsanalisten een reeks cellen te laten markeren en vervolgens deze macro aan te roepen. De macro gebruikt een lus met aan het begin "Voor elke cel in Selectie". Met deze lus wordt "cel" een speciale bereikvariabele. U kunt cell.address of cell.value of cell.row gebruiken om het adres, de waarde of de rij van de huidige cel in de lus te zoeken. Hier is de macro:
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 SubMerk op dat dit een destructieve macro is. Wanneer de gebruiker een celbereik markeert en de macro uitvoert, worden die cellen gewijzigd. Het spreekt voor zich dat u uw macro grondig wilt testen op testgegevens voordat u deze loslaat op echte productiegegevens. Als je een situatie als die van Jean hebt, waarin je constant een kolom moet transformeren met een complexe formule, kan het schrijven van een eenvoudige macro zoals hier geïllustreerd een effectief en tijdbesparend hulpmiddel zijn.
