position of number in string

[Örjan Skoglösa]

I would like to return the numbers from a string, but unfortunately
the string starts with characters and therefore the function Value
returns "0".

I have thought about using the function InStr to get the position of
the first number (the string and the position differ) and then put
"Mid" inside "Val", but if I understand correctly, I can´t use jokers
in InStr to get this position.

How do I find the position of the first number in a string?

TIA
Örjan Skoglösa

 

[Malcolm Smith]

Orjan

Something like this may help. Now bear in mind that I am doing this
straignt onto the newsgroup and before my first coffee of the day...



Function GetPositionFirstNumericInString(sData As String) As Long

Dim nLength As Long
Dim nPosition As Long
Dim sChar As String * 1


nLength = Len(sData)

nPosition = 1
Do While nPosition <= nLength
sChar = Mid$(sData, nPosition, 1)
If sChar >= "0" And sChar <= "9" Then
Exit Do
End If
nPosition = nPosition + 1
Loop

If nPosition <= nLength Then
GetPositionFirstNumericInString = nPosition
Else
GetPositionFirstNumericInString = 0
End If

End Function


I hope that this gets you up and running. If course you may wish to
search for the decimal seperator as well; be it the "." or, as I suspect
from your name, the ",".


Hope that this helps.
Malc
www.dragondrop.com

 

[Word Heretic]

G'day Örjan Skoglösa <[email protected]>,

clng(thestring)

I would like to return the numbers from a string, but unfortunately
the string starts with characters and therefore the function Value
returns "0".

I have thought about using the function InStr to get the position of
the first number (the string and the position differ) and then put
"Mid" inside "Val", but if I understand correctly, I can´t use jokers
in InStr to get this position.

How do I find the position of the first number in a string?

TIA
Örjan Skoglösa

Steve Hudson

Word Heretic, Sydney, Australia
Tricky stuff with Word or words for you.
Email (e-mail address removed)
Products http://www.geocities.com/word_heretic/products.html

Replies offlist may require payment.

 

[Mark Tangard]

CLng shouldn't do it at all. Seems like you'd get a Type
Mismatch. Hmm.... Yes, you do.

Orjan, try this:

Sub GetNumbersFromString
Dim s As String, nums As String, i As Long
s = InputBox("Gimme the string")
If Trim(s) = "" Then Exit Sub
For i = 1 To Len(s)
If Mid$(s, i, 1) >= "0" And Mid$(s, i, 1) <= "9" Then
nums = nums & Mid$(s, i, 1)
End If
Next i
MsgBox nums
End Sub

 

[Cindy Meister -WordMVP-]

Hi Mark,

If Mid$(s, i, 1) >= "0" And Mid$(s, i, 1) <= "9" Then

Wouldn't IsNumeric be a lot easier than testing 0 and 9?

Cindy Meister

 

[Mark Tangard]

Ooooof. Um, yes?

Mid$ on the brain...

 

[Cindy Meister -WordMVP-]

Hi Mark,

Mid$ on the brain...

Well, Malcolm used it first <g>

Cindy

 

[Mark Tangard]

I'm still getting some posts (incl Malc's) out of sync, else I
never would've answered...

MT

 

[Örjan Skoglösa]

Thanks Malcolm, Steve, Mark and Cindy.

I´ll use the first function and the IsNumeric(). I also gather that
jokers are not possible within VBA, only in Search&Replace.

Best regards from Sweden.

Örjan

 

[Mark Tangard]

Örjan,

Some sorts of jokers are possible in working with strings in
VBA, but they wouldn't be usable here. For example, to test
for a string beginning with 'R' and whose last 4 characters
were numbers, you could use:

If mvar Like "R*####" Then

 

[Örjan Skoglösa]

OK, then in the function I could use either

--- If IsNumeric(sChar) Then
or
---- If sChar like "#" Then

but not
Instr(myString, #).

Thanks.

Örjan

 

[Mark Tangard]

Correct.

MT

OK, then in the function I could use either

--- If IsNumeric(sChar) Then
or
---- If sChar like "#" Then

but not
Instr(myString, #).

Thanks.

Örjan