PDA

View Full Version : split a variable



sharathb814
03-25-2009, 08:43 AM
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
03-25-2009, 08:50 AM
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
03-25-2009, 08:53 AM
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
03-25-2009, 09:55 AM
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

sharathb814
03-25-2009, 10:55 AM
@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
03-25-2009, 04:52 PM
Not so good with
i = "abc01230"

mdmackillop
03-25-2009, 05:01 PM
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

mikerickson
03-25-2009, 09:14 PM
A tweak of mdmackillop's previous routine avoids looping.
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

mdmackillop
03-26-2009, 12:52 AM
Hi Mike
Thanks for the improvement. :thumb