| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
separate columns (three letter code)
Hi, does someone know how to separate text from one column into two columns?
I have columns like [dog, KKL] [horse, PPN] Everytime, three capitals in the end (which is a code). Is it possible to separate that, and put it in another column? Thanks in advance. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
separate columns (three letter code)
--Select the range/column needs to be changed.
--From menu DataText to Columns will populate the 'Convert Text to Columns Wizard' --By default the selection is 'Delimited'. Keep the selection and hit 'Next'. --From the Step2 of the Wizard from the options select comma and hit Next. Hit Finish -- Jacob "mariekek5" wrote: Hi, does someone know how to separate text from one column into two columns? I have columns like [dog, KKL] [horse, PPN] Everytime, three capitals in the end (which is a code). Is it possible to separate that, and put it in another column? Thanks in advance. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
separate columns (three letter code)
Thanks Jacob for your help.
But actually, that will not work... I simplified my example, in real life it is: [Mw. dog PKO] [Mr. horse KLI] So I really need a formula, to separate the three letters on the right....I know there is a formula, but I forgot it... Hope you can help me out. Thanks in advance "Jacob Skaria" wrote: --Select the range/column needs to be changed. --From menu DataText to Columns will populate the 'Convert Text to Columns Wizard' --By default the selection is 'Delimited'. Keep the selection and hit 'Next'. --From the Step2 of the Wizard from the options select comma and hit Next. Hit Finish -- Jacob "mariekek5" wrote: Hi, does someone know how to separate text from one column into two columns? I have columns like [dog, KKL] [horse, PPN] Everytime, three capitals in the end (which is a code). Is it possible to separate that, and put it in another column? Thanks in advance. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
separate columns (three letter code)
Hi,
maybe this =LEFT(RIGHT(A1,4),3) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "mariekek5" wrote: Thanks Jacob for your help. But actually, that will not work... I simplified my example, in real life it is: [Mw. dog PKO] [Mr. horse KLI] So I really need a formula, to separate the three letters on the right....I know there is a formula, but I forgot it... Hope you can help me out. Thanks in advance "Jacob Skaria" wrote: --Select the range/column needs to be changed. --From menu DataText to Columns will populate the 'Convert Text to Columns Wizard' --By default the selection is 'Delimited'. Keep the selection and hit 'Next'. --From the Step2 of the Wizard from the options select comma and hit Next. Hit Finish -- Jacob "mariekek5" wrote: Hi, does someone know how to separate text from one column into two columns? I have columns like [dog, KKL] [horse, PPN] Everytime, three capitals in the end (which is a code). Is it possible to separate that, and put it in another column? Thanks in advance. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
separate columns (three letter code)
Use RIGHT()
=RIGHT(A1,3) -- Jacob "mariekek5" wrote: Thanks Jacob for your help. But actually, that will not work... I simplified my example, in real life it is: [Mw. dog PKO] [Mr. horse KLI] So I really need a formula, to separate the three letters on the right....I know there is a formula, but I forgot it... Hope you can help me out. Thanks in advance "Jacob Skaria" wrote: --Select the range/column needs to be changed. --From menu DataText to Columns will populate the 'Convert Text to Columns Wizard' --By default the selection is 'Delimited'. Keep the selection and hit 'Next'. --From the Step2 of the Wizard from the options select comma and hit Next. Hit Finish -- Jacob "mariekek5" wrote: Hi, does someone know how to separate text from one column into two columns? I have columns like [dog, KKL] [horse, PPN] Everytime, three capitals in the end (which is a code). Is it possible to separate that, and put it in another column? Thanks in advance. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
separate columns (three letter code)
Great, this works perfectly.
Is it also possible to get the rest in another column? Like text minus RIGHT(A1,3)...? "Jacob Skaria" wrote: Use RIGHT() =RIGHT(A1,3) -- Jacob "mariekek5" wrote: Thanks Jacob for your help. But actually, that will not work... I simplified my example, in real life it is: [Mw. dog PKO] [Mr. horse KLI] So I really need a formula, to separate the three letters on the right....I know there is a formula, but I forgot it... Hope you can help me out. Thanks in advance "Jacob Skaria" wrote: --Select the range/column needs to be changed. --From menu DataText to Columns will populate the 'Convert Text to Columns Wizard' --By default the selection is 'Delimited'. Keep the selection and hit 'Next'. --From the Step2 of the Wizard from the options select comma and hit Next. Hit Finish -- Jacob "mariekek5" wrote: Hi, does someone know how to separate text from one column into two columns? I have columns like [dog, KKL] [horse, PPN] Everytime, three capitals in the end (which is a code). Is it possible to separate that, and put it in another column? Thanks in advance. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
separate columns (three letter code)
Yes; try
=LEFT(A1,LEN(A1)-3) -- Jacob "mariekek5" wrote: Great, this works perfectly. Is it also possible to get the rest in another column? Like text minus RIGHT(A1,3)...? "Jacob Skaria" wrote: Use RIGHT() =RIGHT(A1,3) -- Jacob "mariekek5" wrote: Thanks Jacob for your help. But actually, that will not work... I simplified my example, in real life it is: [Mw. dog PKO] [Mr. horse KLI] So I really need a formula, to separate the three letters on the right....I know there is a formula, but I forgot it... Hope you can help me out. Thanks in advance "Jacob Skaria" wrote: --Select the range/column needs to be changed. --From menu DataText to Columns will populate the 'Convert Text to Columns Wizard' --By default the selection is 'Delimited'. Keep the selection and hit 'Next'. --From the Step2 of the Wizard from the options select comma and hit Next. Hit Finish -- Jacob "mariekek5" wrote: Hi, does someone know how to separate text from one column into two columns? I have columns like [dog, KKL] [horse, PPN] Everytime, three capitals in the end (which is a code). Is it possible to separate that, and put it in another column? Thanks in advance. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
separate columns (three letter code)
Great, works perfectly!
Thank you! "Jacob Skaria" wrote: Yes; try =LEFT(A1,LEN(A1)-3) -- Jacob "mariekek5" wrote: Great, this works perfectly. Is it also possible to get the rest in another column? Like text minus RIGHT(A1,3)...? "Jacob Skaria" wrote: Use RIGHT() =RIGHT(A1,3) -- Jacob "mariekek5" wrote: Thanks Jacob for your help. But actually, that will not work... I simplified my example, in real life it is: [Mw. dog PKO] [Mr. horse KLI] So I really need a formula, to separate the three letters on the right....I know there is a formula, but I forgot it... Hope you can help me out. Thanks in advance "Jacob Skaria" wrote: --Select the range/column needs to be changed. --From menu DataText to Columns will populate the 'Convert Text to Columns Wizard' --By default the selection is 'Delimited'. Keep the selection and hit 'Next'. --From the Step2 of the Wizard from the options select comma and hit Next. Hit Finish -- Jacob "mariekek5" wrote: Hi, does someone know how to separate text from one column into two columns? I have columns like [dog, KKL] [horse, PPN] Everytime, three capitals in the end (which is a code). Is it possible to separate that, and put it in another column? Thanks in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to change address, city, state zip code into separate columns | Excel Discussion (Misc queries) | |||
| Converting RIC1 code to letter Column/Numeric Row | Excel Discussion (Misc queries) | |||
| Looking for code to separate one line of text into multiple lines in Excel | Excel Worksheet Functions | |||
| doing mail merge with excel the first letter in zip code is missi | Excel Discussion (Misc queries) | |||
| I wish to separate city, state, and zip into 3 separate columns | Excel Worksheet Functions | |||
Linear Mode
