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

 

[Scott T]

This should work for the items where the - or : separate the name from the number. If the - or : is in the middle of the name then it will cut off the name after the - or :.

=IF(ISNUMBER(SEARCH("-",A1)),TRIM(LEFT(A1,SEARCH("-",A1)-1)),TRIM(LEFT(A1,SEARCH(":",A1)-1)))

 

[inactiveuserps07]

@Philip041, this should work for all cases, without affecting numbers, dashes or colons elsewhere in the string:

=TRIM(LEFT(A1,FIND("-",SUBSTITUTE(A1,":","-"),LEN(A1)-8)-1))

 

[tonyyy]

Philip,

If you're comfortable with a vba approach, the following simply deletes all non-alpha characters from the name...

Sub AlphaOnlyArray()
Dim arr As Variant
Dim rng As Range
Dim i As Long, j As Long
Dim str As String

Set rng = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
arr = rng
For i = LBound(arr) To UBound(arr)
    str = arr(i, 1)
    For j = Len(str) To 1 Step -1
        If Not LCase(Mid(str, j, 1)) Like "[a-z]" And Mid(str, j, 1) <> " " Then _
            str = Replace(str, Mid(str, j, 1), "")
    Next j
    arr(i, 1) = Trim(Replace(str, "  ", " "))
Next i
Range("B1:B" & UBound(arr)) = arr
End Sub

Cheers,

tonyyy

 

[Philip041]

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

 

[inactiveuserps07]

Glad it worked out, Philip. Lots of excellent suggestions, approaches and learning opportunities here as well.

 

[Philip041]

Hi all,

Follow up question on this! I'm trying now to extract the number from the end of the text string. I have a text string as before "Johnathan1 Green - 159230" and I want to extract the "159230" but there are a few complications as before:

• 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 had tried measuring the length of the name excluding the number (using the answer in the original question above) and then returning the remaining characters using the RIGHT function but get unstuck due to the name and number being separated by a variable number of characters.

Any ideas?

 

[ukmikeb]

Hi

Try :-

Excel Workbook
	A	B
2	Johnathan - Green - 159230	159230
Philip041

Excel 2007

Cell Formulas
Range	Formula
B2	=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",30)),30))

Assuming that a space precedes the number.

hth

 

[Philip041]

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

 

[Philip041]

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.