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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.