Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns
I have First and last names that were downloaded from a website. The names
appear to be separated by a space, but low and behold when I tried to use text to columns to separate them it did not work. I had to go and change what ever is separating the name to a space on one of the records to get it to work. Any ideas on how to accomplish this and what might possibly be separating the names. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns
It's probably Char(160), a non-breaking space which is used very often in
web sites. Try TTC again, but click on "other" for delimiters, THEN, in the window, hold down <Alt, and key in 0160 using the numbers from the num keypad, *not* the numbers under the function keys. Of course, the box will appear empty, since it *is* a <space. The "Data Preview" window should display the separation ... if my guess was correct. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "SarahJ" wrote in message ... I have First and last names that were downloaded from a website. The names appear to be separated by a space, but low and behold when I tried to use text to columns to separate them it did not work. I had to go and change what ever is separating the name to a space on one of the records to get it to work. Any ideas on how to accomplish this and what might possibly be separating the names. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns
Try these formulae
=LEFT(A1,FIND(CHAR(160),A1&CHAR(160))-1) =SUBSTITUTE(A1&CHAR(160),B1&CHAR(160),"") -- __________________________________ HTH Bob "SarahJ" wrote in message ... I have First and last names that were downloaded from a website. The names appear to be separated by a space, but low and behold when I tried to use text to columns to separate them it did not work. I had to go and change what ever is separating the name to a space on one of the records to get it to work. Any ideas on how to accomplish this and what might possibly be separating the names. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns
Hi SarahJ,
Any ideas on how to accomplish this and what might possibly be separating the names. Select the seperating char and hit Control+c Then do a search and replace, control+v into the search box and space into the replace box. Then replace all. Regards, Jan Karel Pieterse Excel MVP http://www.jkp-ads.com Member of: Professional Office Developer Association www.proofficedev.com |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns
THANKS!!!!!!!!!!!!!!!!!!!!!!! it was Char(160) and the Alt 0160 Worked
perfectly!!!! YIPPIE!!!!!!!!!!!! "RagDyeR" wrote: It's probably Char(160), a non-breaking space which is used very often in web sites. Try TTC again, but click on "other" for delimiters, THEN, in the window, hold down <Alt, and key in 0160 using the numbers from the num keypad, *not* the numbers under the function keys. Of course, the box will appear empty, since it *is* a <space. The "Data Preview" window should display the separation ... if my guess was correct. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "SarahJ" wrote in message ... I have First and last names that were downloaded from a website. The names appear to be separated by a space, but low and behold when I tried to use text to columns to separate them it did not work. I had to go and change what ever is separating the name to a space on one of the records to get it to work. Any ideas on how to accomplish this and what might possibly be separating the names. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns
You're welcome, and appreciate your very enthusiastic feed-back.<g
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "SarahJ" wrote in message ... THANKS!!!!!!!!!!!!!!!!!!!!!!! it was Char(160) and the Alt 0160 Worked perfectly!!!! YIPPIE!!!!!!!!!!!! "RagDyeR" wrote: It's probably Char(160), a non-breaking space which is used very often in web sites. Try TTC again, but click on "other" for delimiters, THEN, in the window, hold down <Alt, and key in 0160 using the numbers from the num keypad, *not* the numbers under the function keys. Of course, the box will appear empty, since it *is* a <space. The "Data Preview" window should display the separation ... if my guess was correct. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "SarahJ" wrote in message ... I have First and last names that were downloaded from a website. The names appear to be separated by a space, but low and behold when I tried to use text to columns to separate them it did not work. I had to go and change what ever is separating the name to a space on one of the records to get it to work. Any ideas on how to accomplish this and what might possibly be separating the names. |
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 |