Thanks a lot Bob....

Sorry for late response...but it worked like a charm.....

Huge thanks...

musa.biralo

Bob Phillips wrote:
Dim sTest As String
Dim iFirstRow As Long
Dim iLastRow As Long
Dim sFirstCol As String
Dim sLastCol As String
Dim rng As Range

sTest = "A1200:AB13000"
Set rng = Range(sTest)
iFirstRow = rng.Row
iLastRow = rng.Rows.Count + iFirstRow - 1
sFirstCol = Replace(Left(sTest, InStr(sTest, ":") - 1), iFirstRow, "")
sLastCol = Replace(Replace(sTest, sFirstCol & iFirstRow & ":", ""),
iLastRow, "")


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"musa.biralo" wrote in message
ups.com...
Hi,
I have to separate a range into alphabetical and numerical part before
and after ":". The problem is i can not use left("text",2) as the
length of string (range) varies....

Eg.

string "A1200:AB13000" --like to get--

lefttext = A, leftnumber = 1200, righttext=AB, rightnumber=13000


the length of string is not fix ....and this is a big problem for
me....might not for you :)

Please help me...or let me know where can i find the info....

Thanks a lot....

musa.biralo