Get text from string after last number

[sharky12345]

Is there a quick way I can extract the text in a string after the last number?

My strings are all letters, then numbers, then letters again - the numbers always fall in the middle of the string, but I need the text at the end, excluding any spaces.

Is it possible please guys?

 

[Peter_SSs]

Formula?
vba?

It would also be good to have a few varied examples of "before" and "after" so we understand exactly what you mean by "excluding any spaces"

 

[Kerryx]

power query?

 

[Scott Huish]

Here's a UDF for that:

Function FindNumber(s As String) As Long
For x = 1 To Len(s)
    If IsNumeric(Mid(s, x, 1)) Then Exit For
Next
FindNumber = Val(Mid(s, x))
End Function

Book3
	A	B
1	abc12345ABC	12345
2	qwertyjflb12djx	12
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=FindNumber(A1)

 

[NdNoviceHlp]

Scott it seems sharky12345 wants the text after the numbers without spaces. Here's a not so eloquent solution. Dave

Sub Test()
Dim i As Integer, NewStr As String
'loop backwards through string
For i = Len(Sheets("Sheet1").Range("A" & 1).Value) To 1 Step -1
    'build string with non numbers
    If Asc(Mid(Sheets("Sheet1").Range("A" & 1).Value, i, 1)) < 48 Or _
    Asc(Mid(Sheets("Sheet1").Range("A" & 1).Value, i, 1)) > 57 Then
    'don't include non print chars
        If Asc(Mid(Sheets("Sheet1").Range("A" & 1).Value, i, 1)) < 8 Or _
        Asc(Mid(Sheets("Sheet1").Range("A" & 1).Value, i, 1)) > 13 Then
        'don't include blank space
        If Asc(Mid(Sheets("Sheet1").Range("A" & 1).Value, i, 1)) <> 32 Then
        'make forward string
        NewStr = Mid(Sheets("Sheet1").Range("A" & 1).Value, i, 1) + NewStr
        End If
    End If
Else
'output result/stop search loop when number found
MsgBox NewStr
Exit For
End If
Next i
End Sub

edit: forgot the code is set to test A1 in sheet1

 

[HighAndWilder]

Is there a quick way I can extract the text in a string after the last number?

My strings are all letters, then numbers, then letters again - the numbers always fall in the middle of the string, but I need the text at the end, excluding any spaces.

Is it possible please guys?

Try this.

Public Function fncFindLastString(s As String) As String
Dim x As Integer

  For x = 2 To Len(s)
    If Not IsNumeric(Mid(s, x, 1)) And IsNumeric(Mid(s, x - 1, 1)) Then Exit For
  Next
  
  fncFindLastString = Mid(s, x)
  
End Function

 

[Kerryx]

Book2
	A	B
1	abc12345ABC	ABC
2	qwertyjflb12djx	djx
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=RIGHT(A1,LEN(A1)-MAX(IF(ISNUMBER(VALUE(MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1))),ROW(INDIRECT("1:" & LEN(A1))))))

 

[Scott Huish]

Function FindLastText(s As String) As String
For x = Len(s) To 1 Step -1
    If IsNumeric(Mid(s, x, 1)) Then Exit For
Next
FindLastText = Mid(s, x + 1)
End Function

 

[HighAndWilder]

Is there a quick way I can extract the text in a string after the last number?

My strings are all letters, then numbers, then letters again - the numbers always fall in the middle of the string, but I need the text at the end, excluding any spaces.

Is it possible please guys?

Or.

Sample Data.xlsm
	A	B	C
1	ddsfsdf5353gffgdgf		gffgdgf
2	ewew4324erwerew		erwerew
3	retert543543gfdhhhf		gfdhhhf
Temp

Cell Formulas
Range	Formula
C1:C3	C1	=TRIM(RIGHT(CONCAT(IF(ISNUMBER((MID(A1,ROW(A1:INDIRECT("A"&LEN(A1))),1)*1)),REPT(" ",100),(MID(A1,ROW(A1:INDIRECT("A"&LEN(A1))),1)))),100))

=TRIM(RIGHT(CONCAT(IF(ISNUMBER((MID(A3,ROW(A3:INDIRECT("A"&LEN(A3))),1)*1)),REPT(" ",100),(MID(A3,ROW(A3:INDIRECT("A"&LEN(A3))),1)))),100))

 

[Rick Rothstein]

Or.

Sample Data.xlsm
	A	B	C
1	ddsfsdf5353gffgdgf		gffgdgf
2	ewew4324erwerew		erwerew
3	retert543543gfdhhhf		gfdhhhf
Temp

Cell Formulas
Range	Formula
C1:C3	C1	=TRIM(RIGHT(CONCAT(IF(ISNUMBER((MID(A1,ROW(A1:INDIRECT("A"&LEN(A1))),1)*1)),REPT(" ",100),(MID(A1,ROW(A1:INDIRECT("A"&LEN(A1))),1)))),100))

=TRIM(RIGHT(CONCAT(IF(ISNUMBER((MID(A3,ROW(A3:INDIRECT("A"&LEN(A3))),1)*1)),REPT(" ",100),(MID(A3,ROW(A3:INDIRECT("A"&LEN(A3))),1)))),100))

I do not believe XL2016 has the CONCAT function in it.