separate "A1200:AB13000" into A, 1200, AB, 1300
 

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

separate "A1200:AB13000" into A, 1200, AB, 1300
 

Hi

Data Text To Columns with semicolon as separator (and be sure there is a
free column as next to one with original data).

Arvi Laanemets


"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

separate "A1200:AB13000" into A, 1200, AB, 1300
 

Hi Arvi
Thanks a lot for the idea.

I am trying to this from Macro so....( i did by recording macro) but
thing is it can not separate you text part and numeric part...

Please let me know if you know other way...
Thanks again.

musa.biralo


Arvi Laanemets wrote:
Hi

Data Text To Columns with semicolon as separator (and be sure there is a
free column as next to one with original data).

Arvi Laanemets


"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

separate "A1200:AB13000" into A, 1200, AB, 1300
 

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

separate "A1200:AB13000" into A, 1200, AB, 1300
 

On 8 Nov 2006 10:57:20 -0800, "musa.biralo" wrote:

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

For a formula solution, you could download and install Longre's free
morefunc.xll add-in from http://xcell05.free.fr/

Then, with your data in A1,

B1: =REGEX.MID($A1,"[A-Z]+|\d+",COLUMNS($A:A))

Copy/drag across as far as required.


--ron

separate "A1200:AB13000" into A, 1200, AB, 1300
 

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