split a variable

[sharathb814]

how do i split a variable into alphabetic & numeric strings for example

dim string1 as string
dim string 2 as string
dim int1 as integer

string1 = "abcd1234"

must be split into two variables where alphabetic part goes to the variable of type string say
string2 = abcd

& numeric part goes to a variable of type integer say

int1 = 1234

thanks in advance.

[Kenneth Hobs]

Welcome to the forum!

Various Regular Expressions methods would be one route. If your data is always strings and then a number, other methods can be used. Is this the case?

Would a worksheet formula method rather than a vba method be preferred?

[sharathb814]

yes my data is always strings & then a number, no a worksheet formula would not work cause the data is in a array & it is array size is quite large.

[mdmackillop]

[VBA]
Sub SplitIt()
Dim i$, Num$, txt$
i = "abc0123"
Num = StrReverse(Val(StrReverse(i)))
txt = Left(i, Len(i) - Len(Num))
MsgBox Num & " - " & txt
End Sub

[/VBA]

[sharathb814]

@MD Thank you,

The solution you have provided seems to be working for now.

Yes the array size is in the order of 5 digits.

[mdmackillop]

Not so good with
i = "abc01230"

[mdmackillop]

[VBA]Sub SplitIt()
Dim i#, Num$, txt$, test$
test = "abc01230"
i = 1
Do Until IsNumeric(Mid(test, i, 1))
i = i + 1
Loop

txt = Left(test, i - 1)
Num = Replace(test, txt, "")

MsgBox txt & " - " & Num

End Sub[/VBA]

[mikerickson]

A tweak of mdmackillop's previous routine avoids looping.
[VBA]Sub SplitIt2()
Dim i$, Num$, txt$
i = "abc01230"

Num = Mid(i, InStr(i, StrReverse(Val(StrReverse(i)))))

txt = Left(i, Len(i) - Len(Num))
MsgBox Num & " - " & txt
End Sub[/VBA]

[mdmackillop]

Hi Mike
Thanks for the improvement.