Remove variable length number from string

[Philip041]

Hello all,

Looking for some help trying to clean some data I have. I have a list of names which have been given a number suffix but unfortunately not in the same way.

My attempt so far: in cell A1 I have "Johnathan Green - 159230". In cell B1 I put "=LEFT(B2, LEN(B2)-9)" to give me just the name, removing the number and also the " - " which was sandwiched between the name and the number.

Complication though:

• The numbers are not always the same length, most often they are 5 or 6 digits long but I have found others which are 3 or 4.
• The " - " sandwich between the name and the number is sometimes a ":"
• Sometimes there are numbers (or hyphens or colons) in the name eg. "Johnathan1 Green - 159230", or "Johnathan - Green - 159230" etc. However as a rule the part I want to get rid of always starts with a " - " or ":" (it just might not be the first time that a " - " ":" appears in the string).

I hope that makes sense. Any help much appreciated!

Philip

 

[Peter_SSs]

Thanks Mike, sorry I didn't make clear that sometimes it could be separate by just a colon eg. "Johnathan Green:159230"..

If you want the result as a number then try this

=RIGHT(SUBSTITUTE(SUBSTITUTE(A2,":"," ")," ",REPT(" ",30)),30)+0

However, if the text might be like Johnathan - Green:000159 and you want the text result 000159 then try

=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A2,":"," ")," ",REPT(" ",30)),30))

 

[Peter_SSs]

I managed to get there in the end. This seems to work ok, it's just previous answers rearranged.

=TRIM(RIGHT(D2,LEN(D2)-FIND("-",SUBSTITUTE(D2,":","-"),LEN(D2)-8)))

Thanks a lot for the help.

If that logic works, then these might too?
Again depends on whether you want the result as a number or as text.

=REPLACE(D2,1,MIN(FIND({"-",":"},D2&"-:",LEN(D2)-9)),"")+0

=TRIM(REPLACE(D2,1,MIN(FIND({"-",":"},D2&"-:",LEN(D2)-9)),""))

 

[dcasciato]

Thanks a lot for the replies. I ended up using the suggestion by Erik and it worked a treat!

Old thread I know - what was Eric's solution - no in the thread...

 

[Fluff]

Please start a new thread for your question & post some sample data. Excel has changed a lot since 2017

 

[Joe4]

Old thread I know - what was Eric's solution - no in the thread...

I believe it is this one here: Remove variable length number from string