How do I use VBA or a function to do a certain type of string work

[christopher ward]

i have a simple spreadsheet of

christopher ward in cell A1
'''''''''''''''''brian wild in cell a2
####jane brown in cell a3
;;;;8888 in cell a4

I now need a VBA function or VBA code Macro that works out in each cell
where the first alphanumeric character falls and returns the character.The
length of the string is not fixed and will have various characters which are
not a-z or 1-9 which are the ones I am interested in.

If you reply to this post thanks in advance it is appreciated

so my answers would be

c
b
j
8


thanks

 

[luis_excelkid]

Hi christopher ward:

i would do this:

Dim linea As Integer
linea = 2
do while <YourWorksheet>.cells(linea,1).value <>""
if (((chr(left(<YourWorksheet>.cells(linea,1).value,1))>=48) and
(chr(left(<YourWorksheet>.cells(linea,1).value,1))<=57))or
((chr(left(<YourWorksheet>.cells(linea,1).value,1))>=65)and
(chr(left(<YourWorksheet>.cells(linea,1).value,1))>=90))or
((chr(left(<YourWorksheet>.cells(linea,1).value,1))>=97)and
(chr(left(<YourWorksheet>.cells(linea,1).value,1))>=122)))=true then
do what you want
End If
linea = linea + 1
Loop

this code check if the firt character is a 1-9, a-z, A-Z, and do something,
add an else sentence to do other things if the first character is a symbol.

I hope this would helpful for you, please let me know,
Bye!

 

[christopher ward]

Hi I will test your theory and come back to you - thank you for your time and
effort

 

[Bob Phillips]

Okay, let's assume that the activecell is A1

Start by creating an Excel defined name (Insert>Name>Define...), with a name
of lenArray and a RefersTo value of =ROW(INDIRECT("1:"&LEN($A1)))

Now use an array formula of

=MID(A1,MIN(IF((ISNUMBER(MATCH(UPPER(MID(A1,lenArray,1)),CHAR(ROW(INDIRECT("65:90"))),0)))+(ISNUMBER(MATCH(--MID(A1,lenArray,1),ROW(INDIRECT("1:10"))-1,0))),lenArray)),1)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[JE McGimpsey]

One way:

Public Function FirstAlphaNumeric(ByVal Text) As String
Dim sTemp As String
Dim sResult As String
Dim i As Long
sTemp = UCase(Text)
If sTemp Like "*[0-9,A-Z]*" Then
For i = 1 To Len(sTemp)
sResult = Mid(sTemp, i, 1)
If sResult Like "[0-9,A-Z]" Then Exit For
Next i
End If
FirstAlphaNumeric = sResult
End Function

 

[Paul Mathews]

Chris, you can try the following code (ascii codes 65-90 represent characters
A-Z, codes 97-122 represent characters a-z, codes 48-57 represent numbers
0-9):

Sub FindFirstAlphaNumeric()
Dim c As Range
Dim StrLen As Integer, i As Integer

For Each c In Range("A1:A100") 'modify data range according to your needs
StrLen = VBA.Len(c)
For i = 1 To StrLen
'Iterate through each string until alphanumeric character is
located...
If (Asc(VBA.Mid(c.Value, i, 1)) >= 65 And _
Asc(VBA.Mid(c.Value, i, 1)) <= 90) Or _
(Asc(VBA.Mid(c.Value, i, 1)) >= 97 And _
Asc(VBA.Mid(c.Value, i, 1)) <= 122) Or _
(Asc(VBA.Mid(c.Value, i, 1)) >= 48 And _
Asc(VBA.Mid(c.Value, i, 1)) <= 57) Then
'...and write that character to the cell in the next column
c.Offset(0, 1).Value = VBA.Mid(c.Value, i, 1)
'exit string iteration for loop
Exit For
End If
Next i
Next c
End Sub

 

[christopher ward]

Guys

thanks for the swift response your answers all worked for my project and
will teach me to know my ASCII code set ( I used to but now i am old ). So
thanks for the time and effort