A reader writes:
One of our staff asked me if there was a way in Excel to take this information:
NAR123456
and replace it with this
123-456
In other words, the person wants to delete the “NAR” and place a hyphen after the first three numbers. Evidently, she has to do this frequently on large spreadsheets. This has got to be an easy thing to do, but I don’t have the skill set needed to come up with the solution. Can you help? Thanks.
Using the string manipulation functions that I covered in the previous two columns, it is possible to make quick work of this problem. However, depending on the layout of the spreadsheet in question, it may be even easier to handle to problem using formulas. An example of this is shown in Figure 1.
Figure 1: Using formulas to manipulate text
The difference between the “Variable” and the “Fixed” columns is that the formula in the variable column assumes that the number of digits after the “NAR” part is variable. The formula for the fixed column assumes that there are exactly 6 digits after the “NAR” part.
If the text can appear anywhere on the worksheet, using formulas isn’t an option because you don’t know ahead of time where the text will show up and it isn’t practical to re-create the formula everywhere it is needed.
The solution in VBA is twofold. First, build a procedure that will convert the text as desired. Second, provide a way to search all of the cells on the worksheet. I’ll only be covering the first issue this week.
Listing 1 shows a simple implementation of one of several approaches to this problem. This implementation is rather simple because it relies on the end user to perform the “searching” for cells that need to be fixed. Then, by assigning a shortcut key to the FixCell macro such as Ctrl + F, one can select individual cells that need to be fixed and press Ctrl + F to fix the cell. FixCell determines if the value in the cell is actually a value that needs to be fixed and, if so, calls the ConvertString function to perform the conversion. Finally, FixCell updates the active cell with the value returned from ConvertString.
Listing 1: A VBA function to convert the string.
' Very simplistic procedure to
' try and fix a cell
' Assigned to shortcut key: ctrl+f
Sub FixCell()
Dim sValue As String
sValue = ActiveCell.value
If Left(sValue, 3) = "NAR" Then
ActiveCell.value = ConvertString(sValue)
End If
End Sub
' Assumes s is a string in the format
' NAR123456
' Converts to 123-456
Function ConvertString(s As String)
Dim sResult As String
' Strip off the "NAR" part
sResult = Right(s, Len(s) - 3)
' Add a hyphen after the third character
sResult = Left(sResult, 3) & "-" & _
Right(sResult, Len(sResult) - 3)
' Return the result
ConvertString = sResult
End Function
Providing a way to search a worksheet for all possible cells that need to be fixed requires an entire column which I will save for another week.