split text/number column into 2 columns

**mb1074** · 01-10-2013, 03:24 PM

I have column which is combination of text and numbers and would like to split it into 2 columns,

i cant use the "Text to Column" option or the RIGHT, LEFT function since the amount of text and the amount of numbers are not the same thruoghout the whole column,

For example:
A1= MYS100
A2= PERA201
A3= LET1894

Thanks in advance
MB1074

**FDibbins** · 01-10-2013, 03:29 PM

assuming your data is in column A, use this in B, copied down...

=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000)))) - Courtesy of:*Ron Coderre

and in C, use this, copied down...
=SUBSTITUTE(A1,B1,"")

**mb1074** · 01-10-2013, 03:59 PM

Thanks a million, worked great,

Although the code sounds like Chinese to me,

one issue, since I need text column before the number column i had swich the codes, so the code for column B I copied in column C and the code for column C I copied to use in column B (changing B1 to C1),

**FDibbins** · 01-10-2013, 04:31 PM

hey, whatever works, right?

Happy to help

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

**mb1074** · 01-10-2013, 04:42 PM

As you said, since I am a new member I wasnt aware about these points, thanks again, I marked it now as solved and added to your reputation,

BTW, would you be so kind and explain to me the code by breaking it down into pieces,