How to identify the position of a character in a string?

[Miles]

Hi,

I need to identify the position of a character in a string
according to a set of rules. Is there a clever way of
doing this?

Example of the problem:

Consider the following string:

Test 1234567 Hello.

I would like to be able to identify the position of a
character in the string. In this case I want the first
character, not being a space, immediately following a
number. In the example this would be H and the position
would be 14.

This was just an example and the rule for which character
is needed could be different. Are there any functions in
VBA for this kind of thing or are there any clever ways of
making them yourself?

Does anybody have a good idea?

BR
/ Miles

 

[Frank Kabel]

Hi
one way: Try the following user defined function:

Public Function char_position(val_str)
Dim i
Dim bnumeric
For i = 1 To Len(val_str)
If IsNumeric(Mid(val_str, i, 1)) Then
bnumeric = True
End If
If Not IsNumeric(Mid(val_str, i, 1)) And bnumeric _
And Mid(val_str, i, 1) <> " " Then
char_position = i
Exit Function
End If
Next
char_position = CVErr(xlErrValue)
End Function

 

[Miles]

Thanks! It works just fine!

BR
/ Miles

-----Original Message-----
Hi
one way: Try the following user defined function:

Public Function char_position(val_str)
Dim i
Dim bnumeric
For i = 1 To Len(val_str)
If IsNumeric(Mid(val_str, i, 1)) Then
bnumeric = True
End If
If Not IsNumeric(Mid(val_str, i, 1)) And bnumeric _
And Mid(val_str, i, 1) <> " " Then
char_position = i
Exit Function
End If
Next
char_position = CVErr(xlErrValue)
End Function



--
Regards
Frank Kabel
Frankfurt, Germany


.

 

[Tom Ogilvy]

Sounds like you want to use Regular Expressions. The are not implemented
directly in VBA, but by creating a reference to the regular expressions
library, you can use them. Here is a start:

http://support.microsoft.com/default.aspx?scid=kb;en-us;818802&Product=vbb
HOW TO: Use Regular Expressions in Microsoft Visual Basic 6.0

 

[JE McGimpsey]

VBA doesn't have a specific method for "the first character, not being a
space, immediately following a number".

If you always have a single space character following your number which
follows a word separated by a space, you could use

Dim nPos As Long
nPos = InStr(InStr(1, sString, " ") + 1, sString, " ") + 1

obviously the formula would be different for different rules.

 

[Adrian]

Does it have to be in VBA?
There's a worksheet function =FIND(find_text,within_text)
that does exactly what you describe.

ie. =FIND("H","Test 1234567 Hello") would return 14.

Ok, so more complex rules mean you'll have to use a custom
function probably....