Extract alphabet from string.

[Bill Choy]

Hi all,

Are there any suggestion to extract the first few alphabet
from a string? For example, "RC125", I want to scan this
string from the first character and extract "RC" to the
other cell. Please suggest a method to recognize between A-
Z and 0-9. Thanks.

Bill

 

[Jim Cone]

Bill,

This works for me...

'--------------------------------------------
'Jim Cone 02/19/2004

Sub FindAlphasOnLeft()
Dim lngNum As Long
Dim lngLength As Long
Dim strExtract As String
Dim strCharacters As String

strCharacters = Range("F8").Text & CStr(1)
lngLength = Len(strCharacters)

For lngNum = 1 To lngLength
If Mid$(strCharacters, lngNum, 1) Like "#" Then
strExtract = Left$(strCharacters, lngNum - 1)
Exit For
End If
Next 'lngNum
If Len(strExtract) Then
Range("J8").Value = strExtract
Else
Range("J8").Value = "No Alpha on left side"
End If
End Sub
'--------------------------------------------

Regards,
Jim Cone
San Francisco, CA

 

[David Coleman]

Hi Bill

This will pull out all of the alphanumeric characters from the first one to
the last - e.g.

ab145 gives ab145
$@ab145 gives ab145
ab$@145 gives ab
$@ab$@145 gives ab

you can hack the alphanum function to allow / dis-allow whatever characters
that you want....

HTH

David

############################################

Function alphanum(letter As String)
Select Case (letter)
Case "a" To "z", "A" To "Z", 0 To 9: alphanum = True
Case Else: alphanum = False
End Select
End Function


Sub macro1()
Dim x, y As Integer
Dim tocheck, result As String
Dim done, doing, started As Boolean

x = 1
y = 1
done = False
doing = True
started = False

While (Range("A" & x).Value <> "")
y = 1
result = ""
tocheck = Range("A" & x).Value
While ((y <= Len(tocheck)) And (Not (done)))
If (alphanum(Mid(tocheck, y, 1))) Then
result = result & Mid(tocheck, y, 1)
started = True
Else
If (started) Then doing = False
End If
If (Not (doing)) Then done = True
y = y + 1
Wend

doing = True
done = False
started = False
Range("b" & x).Value = result
x = x + 1
Wend
End Sub


############################################

 

[Bill Choy]

Hi Jim,

This is what I'm looking for. Thanks.

There are two syntax I don't understand.
1. What's the function of CStr(1)?
2. What's the function of Like "#"?

Bill

 

[Jim Cone]

Bill,

The cell text might not have any numbers in it.
So, the number 1 is added to the end of the text to provide a
stop or end point for the loop. "Cstr" converts the number to
a string. Cstr is not absolutely necessary in this case as it would
have been done automatically, but it does tell you what is going on.

The "like" operator is used to compare two strings.
The "#" tells it to look for any single digit (0-9).
If a number is found, then the code puts all characters to the
left of the digit into the strExtract variable and exits the loop.

In appears to me that the "like" operator is often overlooked by
programmers. It could be worth your time to review the help
topic for "like" and try out some of the examples.

Regards,

Jim Cone
San Francisco, CA

 

[Bill Choy]

Hi Jim,

Is the "#" a pre-define function to represent 0-9 in VB?
If I want to look for A-Z, which symbol shoud I do?

Thanks.

 

[Jim Cone]

Bill,

Is the "#" a pre-define function to represent 0-9 in VB?
It is a "pattern character" used by the Like operator. It is not a function.

If I want to look for A-Z, which symbol should I do?
"[A-Z]"

Use it something like the following...

Dim strChar as string
strChar = "D"
If strChar like "[A-Z]" Then...

Please look at the help file.

Regards,
Jim Cone
San Francisco, CA