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
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

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"
 

Watch MrExcel Video

Forum statistics

Threads
1,123,047
Messages
5,599,497
Members
414,315
Latest member
Yolanda5050

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