Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text to columns question
I am using a program that will save a report as Excel. In this report I have
one column with First and Last name. I try text to columns to separate but it won't work. I have to physically put a space in between the first and last name to achieve this. Is there a way to do this without having to manually put in the space? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text to columns question
Hi,
Text to colums requires a delimeter or for your string to be split 'fixed width' and it seems unlikely all your first names will be of the same length. Post some sample data and someone may be able to help. Mike "Heidi" wrote: I am using a program that will save a report as Excel. In this report I have one column with First and Last name. I try text to columns to separate but it won't work. I have to physically put a space in between the first and last name to achieve this. Is there a way to do this without having to manually put in the space? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text to columns question
Keith Atkinson
Jessica Bertrum Gary Blenker Pete Brang Jeff Davies Perry Ellering Shannon Ellering Kristen Finken AIMEE GERADS JON HINNENKAMP KENNY HINNENKAMP Jeff Houston Ryan Imdieke This is an example of the column of names. Excel is not recognizing the space that is already between the two names, and when I add a space to conert to two columns, then I have to physically remove the extra space. "Mike H" wrote: Hi, Text to colums requires a delimeter or for your string to be split 'fixed width' and it seems unlikely all your first names will be of the same length. Post some sample data and someone may be able to help. Mike "Heidi" wrote: I am using a program that will save a report as Excel. In this report I have one column with First and Last name. I try text to columns to separate but it won't work. I have to physically put a space in between the first and last name to achieve this. Is there a way to do this without having to manually put in the space? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text to columns question
What is between the First and Last names now? If they are separated with
something that is not a space, but it looks like a space, then you can do this to separate them. Select any one cell with the mysterious "space" in it and highlight (select) this character, then press Ctrl+C to copy it into the Clipboard. Now select all of your First/Last names; then go into the Text To Columns dialog box and choose delimited in Step 1; on Step 2, put your cursor in the blank box next to the "Other" checkbox and press Ctrl+V to paste the contents of the Clipboard (which contains the mysterious space character) into it (this will automatically select the "Other" checkbox). Note... the names should now show with a separator between them in the table area of the dialog box. Click the Finish button and your names should all be separated. -- Rick (MVP - Excel) "Heidi" wrote in message ... I am using a program that will save a report as Excel. In this report I have one column with First and Last name. I try text to columns to separate but it won't work. I have to physically put a space in between the first and last name to achieve this. Is there a way to do this without having to manually put in the space? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text to columns question
Heidi,
Lets suppose yo have Keith Atkinson in A1, what does the formula below return? =CODE(MID(A1,6,1)) Mike "Heidi" wrote: Keith Atkinson Jessica Bertrum Gary Blenker Pete Brang Jeff Davies Perry Ellering Shannon Ellering Kristen Finken AIMEE GERADS JON HINNENKAMP KENNY HINNENKAMP Jeff Houston Ryan Imdieke This is an example of the column of names. Excel is not recognizing the space that is already between the two names, and when I add a space to conert to two columns, then I have to physically remove the extra space. "Mike H" wrote: Hi, Text to colums requires a delimeter or for your string to be split 'fixed width' and it seems unlikely all your first names will be of the same length. Post some sample data and someone may be able to help. Mike "Heidi" wrote: I am using a program that will save a report as Excel. In this report I have one column with First and Last name. I try text to columns to separate but it won't work. I have to physically put a space in between the first and last name to achieve this. Is there a way to do this without having to manually put in the space? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text to columns question
Thank you very much!!! What a time saver!!
"Rick Rothstein" wrote: What is between the First and Last names now? If they are separated with something that is not a space, but it looks like a space, then you can do this to separate them. Select any one cell with the mysterious "space" in it and highlight (select) this character, then press Ctrl+C to copy it into the Clipboard. Now select all of your First/Last names; then go into the Text To Columns dialog box and choose delimited in Step 1; on Step 2, put your cursor in the blank box next to the "Other" checkbox and press Ctrl+V to paste the contents of the Clipboard (which contains the mysterious space character) into it (this will automatically select the "Other" checkbox). Note... the names should now show with a separator between them in the table area of the dialog box. Click the Finish button and your names should all be separated. -- Rick (MVP - Excel) "Heidi" wrote in message ... I am using a program that will save a report as Excel. In this report I have one column with First and Last name. I try text to columns to separate but it won't work. I have to physically put a space in between the first and last name to achieve this. Is there a way to do this without having to manually put in the space? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text to Columns Question | Excel Discussion (Misc queries) | |||
Text to Columns Question | Excel Discussion (Misc queries) | |||
Text to columns question | Excel Worksheet Functions | |||
Question about Data-Text to Columns command | Excel Discussion (Misc queries) | |||
Text to Columns Question | Excel Worksheet Functions |