Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to split cells | Excel Worksheet Functions | |||
How to Split the contents of cells across multiple cells | New Users to Excel | |||
how to split cells? | New Users to Excel | |||
Split cells | New Users to Excel | |||
split cells | Excel Discussion (Misc queries) |