Text to Colums

[blander]

Hi Guys,

I have some data in one colum that contains a full name (first *space*
last)

I want to split the names into two colums, and i know that you can do
it via text to colums, but it dosent see the space correctly..

If the names are seperated by two spaces it does it correctly.. but it
wont see the sigle space as a delimiter it just keeps the data in the
one (original) colum...

Any ideas..

Cheers

 

[Guest]

The "space" may not be a normal space it may be a non-breaking or special
width.
Try find and replace with a copy of the "space" in the find and a regular
space in the replace, they try text to column

 

[Guest]

Insert 2 helper columns to the right of the column containing the names.

The example assumes the names are in column A starting at row 1

In cell B1 enter the following formula: =LEFT(A1,FIND(" ",A1,1))
In cell C1 enter the following formula: =TRIM(RIGHT(A1,LEN(A1)-FIND("
",A1,1)))

Copy both formulas to the row containing the last name. Select all the
cells in both formula columns and press Ctrl+C to copy them, and then click
EDIT on the menu and select PASTE SPECIAL. In the dialog box locate the
VALUES option button and click OK. The formulas are replaced with their
respective values. YOu can now remove the orginal column containing the
names from the spreadsheet w/o causing any problems.

 

[blander]

Ok, so it will do some of the data but not all of it.. even if it ha
spaces between the data....


Like you can see in this shot, the top value did not change (eve
though it had a space) but the ones underneath did change.

+-------------------------------------------------------------------
|Filename: data.JPG
|Download: http://www.excelforum.com/attachment.php?postid=5154
+-------------------------------------------------------------------

 

[blander]

Thanks guys

Really appreciate your help!