PDA

View Full Version : Solved: removing part of string

thmh
09-07-2011, 09:24 AM
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
09-07-2011, 10:22 AM
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
09-07-2011, 10:25 AM
Another way

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

thmh
09-07-2011, 10:36 AM
formula is good but i need vba code

Kenneth Hobs
09-07-2011, 10:38 AM
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

sdruley
09-07-2011, 02:36 PM
See attached example. The numbers can be anywhere in the string.

thmh
09-08-2011, 08:13 AM
Kenneth yore code gave me error see picture,
punting sdruley function and your sub together worked fine,

tnx

here is code

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

http://www.easilysharing.com/images/16453081600596943540.jpg
http://vbaexpress.com/forum/E:%5CUsers%5Cmarinela%5CDesktop%5CCapture.JPG

Kenneth Hobs
09-08-2011, 08:33 AM
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
09-08-2011, 11:04 AM
sdruley attached file with some examples , just copy examples in column A , if you remove debug line next line will error

Kenneth Hobs
09-08-2011, 11:50 AM
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.
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