Data scrub

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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"
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,008
Messages
5,834,853
Members
430,324
Latest member
bosphoruskid

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top