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