Solved: removing part of string

[thmh]

hy,
have data in column A , and need to remove part of string starting empty space following with number ...

something like this ,

replace" num*" , with ""
example:

bla bla 224555 --result--> bla bla

[Bob Phillips]

Try

=TRIM(SUBSTITUTE(A2,LOOKUP(9.99999999999999E+307,--MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),ROW(INDIRECT("1:" &LEN(A2))))),""))

[Bob Phillips]

Another way

=LEFT(A2,FIND(CHAR(1),SUBSTITUTE(A2," ",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-1)

[thmh]

formula is good but i need vba code

[Kenneth Hobs]

[VBA]Sub Test_RemoveTrailingNumbers()
Dim cell As Range
For Each cell In Range("A1", Range("A" & Rows.Count).End(xlUp))
cell.Value2 = RemoveTrailingNumbers(cell.Value2)
Next cell
End Sub

Function RemoveTrailingNumbers(aString As String) As String
Dim a() As String, s As String
a = Split(aString, " ")
Debug.Print a(UBound(a)), IsNumeric(a(UBound(a)))
If IsNumeric(a(UBound(a))) Then
ReDim Preserve a(0 To (UBound(a) - 1))
s = Join(a, " ")
Else
s = aString
End If
RemoveTrailingNumbers = s
End Function[/VBA]

[sdruley]

See attached example. The numbers can be anywhere in the string.

[thmh]

Kenneth yore code gave me error see picture,
punting sdruley function and your sub together worked fine,

tnx

here is code
[vba]

Function NoNumber(S As String) As String
Dim L, L1, Str
Str = S
L = Len(Str)
For p = L To 2 Step -1
n = IsNumeric(Mid(Str, p - 1, 1))
If n Then
x = x + 1
A = Left(Left(Str, p - 1), Len(Left(Str, p - 1)) - 1)
'B = Right(Str, L - p - x + 1)
Str = A '& B
End If
Next p
NoNumber = Str
End Function

Sub Test_NoNumber()
Dim cell As Range
For Each cell In Range("A1", Range("A" & Rows.Count).End(xlUp))
cell.Value2 = NoNumber(cell.Value2)
Next cell
End Sub

[/vba]

[Kenneth Hobs]

Can you post the file where my function failed and say which cells to test?

With the string that you posted it worked fine. Obviously, Debug.Print is not needed. It would fail later if that line fails though.

[thmh]

sdruley attached file with some examples , just copy examples in column A , if you remove debug line next line will error

[Kenneth Hobs]

I could not reproduce an error per se. The last result showed #VALUE because the string was only a number. That case can be coded for.

My function varied from NoNumber() because I read the request to trim a string with a trailing space character and a number.

IF the goal was to strip all numbers from a string, then I would have solved the problem in another way.

The "skjfslkj 23 545 " contained a trailing space character so my UDF did not trim it.

"2400987234" contains no space character so my UDF did not change it but did show #VALUE which is easily coded for as I said.

This one should replace all number strings if that was your goal.
[vba]Function RemoveWordNumbers(aString As String) As String
Dim a() As String, s As String, i As Integer
a = Split(aString, " ")
For i = 0 To UBound(a)
If IsNumeric(a(i)) Then a(i) = ""
Next i
RemoveWordNumbers = Trim(Join(a, " "))
End Function[/vba]