Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns help
Hi
I have a column with name like below McAllister Mr & Mrs E. Hay Mrs E. Farquhar Rev. G. & Mr M. Russell Mrs M. Cuthbert Mr & Mrs J. Cuthbert Mr R. Cuthbert Miss Judith Weller Mr & Mrs M. Doherty Mrs C. Stewart Mr & Mrs J. Ridges Mr W. Is it possibe to do Text to 2 Columns on them all in one go? I'm having to do long names like McAllister Mr & Mrs E. into 2 colums McAllister & Mr & Mrs E. & short names Hay Mrs E. into Hay & Mrs E. seperately which is going to take ages as there are 14 similar pages Can anyone offer a solution please -- Martin ©¿©¬ |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns help
With names in Col A try the formulas in B and C
B1 =TRIM(LEFT(A1,FIND(" ",A1))) C1 =TRIM(RIGHT(A1,FIND(" ",A1))) OR Text to Columns with Space delimiter so that the first column will have names and combine the remaining 3 or 4 columns using Concatenate() function If this post helps click Yes --------------- Jacob Skaria "Martin ©¿©¬ @nohere.net" wrote: Hi I have a column with name like below McAllister Mr & Mrs E. Hay Mrs E. Farquhar Rev. G. & Mr M. Russell Mrs M. Cuthbert Mr & Mrs J. Cuthbert Mr R. Cuthbert Miss Judith Weller Mr & Mrs M. Doherty Mrs C. Stewart Mr & Mrs J. Ridges Mr W. Is it possibe to do Text to 2 Columns on them all in one go? I'm having to do long names like McAllister Mr & Mrs E. into 2 colums McAllister & Mr & Mrs E. & short names Hay Mrs E. into Hay & Mrs E. seperately which is going to take ages as there are 14 similar pages Can anyone offer a solution please -- Martin ©¿©¬ |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns help
On Mon, 18 May 2009 06:14:06 -0700, Jacob Skaria
wrote: With names in Col A try the formulas in B and C B1 =TRIM(LEFT(A1,FIND(" ",A1))) C1 =TRIM(RIGHT(A1,FIND(" ",A1))) OR If this post helps click Yes --------------- Hi Jacob This is my first time trying this, Can u explain in more detail please Text to Columns with Space delimiter so that the first column will have names and combine the remaining 3 or 4 columns using Concatenate() function -- Martin ©¿©¬ "Martin ©¿©¬ @nohere.net" wrote: Hi I have a column with name like below McAllister Mr & Mrs E. Hay Mrs E. Farquhar Rev. G. & Mr M. Russell Mrs M. Cuthbert Mr & Mrs J. Cuthbert Mr R. Cuthbert Miss Judith Weller Mr & Mrs M. Doherty Mrs C. Stewart Mr & Mrs J. Ridges Mr W. Is it possibe to do Text to 2 Columns on them all in one go? I'm having to do long names like McAllister Mr & Mrs E. into 2 colums McAllister & Mr & Mrs E. & short names Hay Mrs E. into Hay & Mrs E. seperately which is going to take ages as there are 14 similar pages Can anyone offer a solution please -- Martin ©¿©¬ |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns help
1. Using formulas
With the name McAllister Mr & Mrs E. in A1 try the below formulas in B1 and C1. If that does work copy the formula down to the number of rows where you have the names in A1. 2. Text to Columns Select the data range. From menu DataTExt to Columns will display the first few samples in teh data preview area.Next will take you to step 2. By default tab will be ticked. Take off that and check only Space. and Next This will put a line breaker in between the spaces. NextFinish. this will split the data between spaces. Maintain Column A as such For B,C,D and E might have information which needs to be combined. Use a formula in Col F such as In cell F1 =B1&" "&C1&" "&D1&" "&E1 will combine the initials and titles with a space delimiter...Now copy the formula to all rows.. Once done copy paste special values to the next column or to the same column........ -- If this post helps click Yes --------------- Jacob Skaria "Martin ©¿©¬ @nohere.net" wrote: On Mon, 18 May 2009 06:14:06 -0700, Jacob Skaria wrote: With names in Col A try the formulas in B and C B1 =TRIM(LEFT(A1,FIND(" ",A1))) C1 =TRIM(RIGHT(A1,FIND(" ",A1))) OR If this post helps click Yes --------------- Hi Jacob This is my first time trying this, Can u explain in more detail please Text to Columns with Space delimiter so that the first column will have names and combine the remaining 3 or 4 columns using Concatenate() function -- Martin ©¿©¬ "Martin ©¿©¬ @nohere.net" wrote: Hi I have a column with name like below McAllister Mr & Mrs E. Hay Mrs E. Farquhar Rev. G. & Mr M. Russell Mrs M. Cuthbert Mr & Mrs J. Cuthbert Mr R. Cuthbert Miss Judith Weller Mr & Mrs M. Doherty Mrs C. Stewart Mr & Mrs J. Ridges Mr W. Is it possibe to do Text to 2 Columns on them all in one go? I'm having to do long names like McAllister Mr & Mrs E. into 2 colums McAllister & Mr & Mrs E. & short names Hay Mrs E. into Hay & Mrs E. seperately which is going to take ages as there are 14 similar pages Can anyone offer a solution please -- Martin ©¿©¬ |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns help
Thanks Jacob
For some reason that wouldn't work for me -- Martin ©¿©¬ On Mon, 18 May 2009 09:47:20 -0700, Jacob Skaria wrote: 1. Using formulas With the name McAllister Mr & Mrs E. in A1 try the below formulas in B1 and C1. If that does work copy the formula down to the number of rows where you have the names in A1. 2. Text to Columns Select the data range. From menu DataTExt to Columns will display the first few samples in teh data preview area.Next will take you to step 2. By default tab will be ticked. Take off that and check only Space. and Next This will put a line breaker in between the spaces. NextFinish. this will split the data between spaces. Maintain Column A as such For B,C,D and E might have information which needs to be combined. Use a formula in Col F such as In cell F1 =B1&" "&C1&" "&D1&" "&E1 will combine the initials and titles with a space delimiter...Now copy the formula to all rows.. Once done copy paste special values to the next column or to the same column........ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining Text from 2 Columns into 1 then Deleting the 2 Columns | Excel Worksheet Functions | |||
help with sorting text in columns to match other columns | Excel Discussion (Misc queries) | |||
merge text from 2 columns into 1 then delete the old 2 columns | Excel Worksheet Functions | |||
Excel is automatically doing "text to columns" upon paste text. | Excel Discussion (Misc queries) | |||
Linking text columns with text and data columns | Excel Worksheet Functions |