Solved: removing part of string

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

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))))),""))

Another way

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

formula is good but i need vba code

[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]

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

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]


http://www.easilysharing.com/images/...0596943540.jpg
http://vbaexpress.com/forum/E:%5CUse...%5CCapture.JPG

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.

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

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]