Split Cells
 

I have a worksheet that I need to split data in one cell into another. Text
to columns not working because there is no common denominator. An example:
Group & Organization Management 1059-6011. I need the words in column A and
the numbers in column B. I can't figure this out. Help.

Split Cells
 

Assume that you are having the below value in A1 cell.

A1
Group & Organization Management 1059-6011


Method 1:-
If the Numbers are appearing in the end of the cell content (i.e.) last word
of that cell, then use the below method

Paste this formula in C1 cell
=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1,"
","")))))

Paste this formula in B1 cell
=TRIM(SUBSTITUTE(A1,C1,""))
This will give you the result once the C1 cell is filled with the above
formula.


Method 2:-
If the position of the Number is unpredictable, then use the below method

Paste this formula in C1 cell
=MID(A1,MATCH(0,(ISERROR(MID(A1,ROW(INDIRECT("1:"& LEN(A1))),1)*1)*1),0),LEN(A1)-SUM((ISERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1) *1))))
This is an array formula. So copy the above formula and place the cursor in
C1 cell and press F2 and give Cntrl+V and instead of enter Press
Cntrl+Shift+Enter.

Paste this formula in B1 cell
=TRIM(SUBSTITUTE(A1,C1,""))
This will give you the result once the C1 cell is filled with the above
array formula.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"slfalconi" wrote:

I have a worksheet that I need to split data in one cell into another. Text
to columns not working because there is no common denominator. An example:
Group & Organization Management 1059-6011. I need the words in column A and
the numbers in column B. I can't figure this out. Help.

Split Cells
 

On Tue, 19 Jan 2010 16:44:01 -0800, slfalconi
wrote:

I have a worksheet that I need to split data in one cell into another. Text
to columns not working because there is no common denominator. An example:
Group & Organization Management 1059-6011. I need the words in column A and
the numbers in column B. I can't figure this out. Help.

A1: Your String
B1: =LEFT(A1,LEN(A1)-LEN(C1)-1)
(won't look correct until you enter C1)
C1: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

Select B1:C1
Edit/Copy

Select A1
Edit/Paste Special/Values

--ron