MrExcel.com - Your One Stop for Excel Tips & Solutions

Replace Text In Cells

October 15, 2002 - by

Jean asked this week's Excel question:

I am trying to convert a supplier price book in Excel to match their UPC codes to our UPC code system. Their price book has a column in Excel with a series of numbers eg. 000004560007 OR cel000612004. What I have to do is two fold. I need to take the first three zeroes out without deleting any other 0's in the cell. Next, I have to append a 3 digit code "upc" before the first numeric digit in the cell. This will be an ongoing need. I will have to train several people to use the Excel system.

It sounds like Jean already considered using Edit-Replace to get rid of the three zeroes. This would not work because doing an edit replace on "000004560007" would cause both occurrences of 000 to disappear.

First I want to suggest that Jean insert a temporary column next to the current price codes, write a formula to do the transformation, then use Edit-Copy, Edit-PasteSpecial-Values to copy the formula back over the original price codes.

The poorly documented REPLACE() function will do the trick in this case. I was disappointed with the implementation of REPLACE(). I would have thought it would ask for a specific text to replace, but instead it asks for the user to figure out character positions to replace. REPLACE will replace part of a text string with a new string. The four arguments that you pass to the function are the cell containing the old text, the position of the character in old text you want to replace, the number of characters to replace, and the new text to use.

My simplifying assumption is that the three zeroes represent the onset of the UPC code in the string. Thus, there is no need to search for the first numeric character in the cell. Once the formula finds the three zeroes, it can replace those three zeroes with the string "upc".

To find the location of the first occurrence of "000" in the text, you would use this formula:

=FIND(A2,"000")

The formula that Jean would need to enter in cell B2 would be:

=REPLACE(A2,FIND("000",A2),3,"upc")

My thought is to have the pricing analysts highlight a range of cells and then invoke this macro. The macro will use a loop with "For each cell in Selection" at the beginning. With this loop, "cell" becomes a special range variable. You can use cell.address or cell.value or cell.row to find the address, value or row of the current cell in the loop. Here is the 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 Sub

Note that this is a destructive-type macro. When the user highlights a range of cells and runs the macro, those cells will be changed. It goes without saying that you want to thoroughly test your macro on test data before you set it loose on real production data. When you have a situation like Jean's where you will constantly be required to transform a column using a complex formula, writing a simple macro like the one illustrated here can be an effective and time-saving tool.