Best way to remove a number at the end of a name?

[Mr_Phil]

When I import performance data I get a column that is a mashup of the work order and the tech name.

8080222222 -Boliver.Shagglenasty
8236541245 -Boliver.Shagglenasty
2589764318 -Boliver.Shagglenasty1
9371824679 -Boliver.Shagglenasty1
7193824613 -Boliver.Shagglenasty2

I don't need the wo # but I do need the name.

I use

Please Login or Register  to view this content.

to isolate the name. The 19 is the actual number but won't look right with the above because I changed up the WO numbers to avoid any issues with proprietary info.

Then I have an automated find/repl that gets the period out and replaces it with a space.

Please Login or Register  to view this content.

Usually I'm good. But, the source data sometimes has a numeric attachment as shown above. My method doesn't see or remove that. When I roll up name-based data the numerics are not counted since they don't match the other name.

I need to fix it so that any numeral(s) at the end is/are removed. And right now I am drawing a blank.

Any ideas? Any discussion welcomed. No idea is too crazy. Thanks for reading everyone.

[mikeava]

Have you tried Text to columns to separate the work order number, name and number at the end?

[mikeava]

Go to the Data tab
Click on Text to Columns
Select Fixed Width

Adjust the lines accordingly then hit Finish.

Results are like this

[Gregb11]

Well, here's an ugly formula, but it doesn't matter if there are any numbers after the name or not, nor does it care how many digits are after the name.
It does assume that the name always starts at the 13 character. If that's not true, more formula can be added to handle that as well. (This formula assumes your data is in cell A1.

=TRIM(LEFT(SUBSTITUTE(TRIM(MID(A1,13,500)),"."," "),MIN(FIND({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(TRIM(MID(A1,13,500)),"."," ")&" 0123456789"))-1))

[Haluk]

If you want to use VBA, select the range with your mouse and run the below code;

Please Login or Register  to view this content.

[Bo_Ry]

Please try

=MID(LEFT(A2,LEN(A2)+1-MATCH(1,INDEX(-RIGHT(A2&0,{1,2}),))),FIND("-",A2)+1,50)