Data scrub

[texasalynn]

I have a file with a list of names and some have a number with the name. I need to find a way to take off the number.

For example I will have
Aldi #6076
or Aldi Grocery 9711
I want
Aldi
Aldi Grocery

The only common thing about the data is that the name is first and the numbers are at the end.

Any ideas?

Thanks
texasalynn

 

[Damon Ostrander]

Hi Tex,

Here is a macro you can use to do this. The macro (and its helper function) will strip trailing numbers and blanks from the contents of all selected cells.

Sub StripNumbers()
' This macro strips numbers from the ends of strings in
' the selected cells. Also removes trailing blanks.
' Example: changes "Aldi Grocery 9722" to "Aldi Grocery"
Dim Cel As Range
For Each Cel In Selection
Cel.Value = Nstrip(Cel.Value)
Next Cel
End Sub

Function Nstrip(S As String) As String
Nstrip = RTrim(S)
If IsNumeric(Right(Nstrip, 1)) Then
Nstrip = Left(Nstrip, Len(Nstrip) - 1)
Nstrip = Nstrip(Nstrip)
End If
End Function

This code should be placed in a standard macro module. To do this, go to the Visual Basic Editor (keyboard Alt-TMV), create a new macro module (Alt-IM) and paste the code there. It is then immediately available for use.

You can also use the helper function Nstrip by itself as a user-defined function (UDF) to return the trimmed string directly in a cell. For example, if cell B4 contains

"Aldi Grocery 9711"

then the cell formula

=Nstrip(B4)

will yield the value "Aldi Grocery"