Test String manipulatiion

Posted by David on July 27, 2000 11:11 PM

I have a text string call "step"
I want make "stepnum" = to the numbers on the end. There are always an unknown amount of letters then a space and then 3 or 4 numbers on the end. I would rather have just the numbers but I will take just the last four characters because if it is three digits then it will always just be a space then 3 digits or four digits.
Thanks I appreciate the help.

Posted by Celia on July 31, 0100 12:50 AM

David
You are too kind (blush!). It seems to me that there has most certainly been no shortage whatsoever of some great expert help (Ivan, Ryan, et alia).
Celia

Posted by david on July 28, 0100 2:28 AM

Haven't tried working with that yet but I will. But my thought is there might be more than one space. MeaningI can have
a word
a space
aword
a space
and a number

such as

"install hardware 2310"
"Test and pack 2410"
"clean parts 100"

Posted by Christmas Carol on July 28, 0100 5:13 AM

If you have one space in your string, followed by numbers:

stepnum = RIGHT(stepnum, len(stepnum) - instr(stepnum, " "))

But, if there is more than one space:=

Sub test()
Dim stepnum As String

stepnum = "Mind The Gap 1234"

While InStr(stepnum, " ") > 0
stepnum = Right(stepnum, Len(stepnum) - InStr(stepnum, " "))
Wend

End Sub

Posted by Ryan on July 28, 0100 5:15 AM

David,

Here are two different procedures. The bottom one can only be used in XL2000. The top one will only work if the numbers are all at the end. Hope it helps.

Ryan

Sub FindNumber()
Dim stepnum As String
stepnum = ""

For x = 1 To Len(step)
If IsNumeric(Mid(step, x, 1)) Then stepnum = stepnum + Mid(step, x, 1)
Next x
End Sub

Sub xl2000()
stepnum = Right(step, Len(step) - InStrRev(step, " "))
End Sub

Posted by Celia on July 28, 0100 5:39 AM

David

Try this macro :-

Sub GetStepnum()
Dim step As String, stepnum As String
If InStr(step, " ") = 0 Then
stepnum = ""
Else
stepnum = Right(step, Len(step) - InStr(step, " "))
Do
stepnum = Right(stepnum, Len(stepnum) - InStr(stepnum, " "))
Loop Until InStr(stepnum, " ") = 0
End If
End Sub

If you don't want to use a macro, try this worksheet formula :-

=RIGHT(step,LEN(step)-FIND("*",SUBSTITUTE(step," ","*",LEN(step)-LEN(SUBSTITUTE(step," ","")))))

Celia

Posted by David on July 30, 0100 9:45 PM

Thanks Celia That worked great after a very small change in which I decided to call it as a function.

Thank you celia and ryan for all your help and welcome back celia. I think I speak for all of us when I say we missed you.

Posted by Ryan on July 27, 0100 11:28 PM

David,

Here is a way to get the numbers after a space in your string:

stepnum = Mid(step, InStr(1, step, " "))

Hope it helps!

Ryan