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


--
blander
------------------------------------------------------------------------
blander's Profile: http://www.excelforum.com/member.php...o&userid=36486
View this thread: http://www.excelforum.com/showthread...hreadid=568945

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

"blander" wrote:


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


--
blander
------------------------------------------------------------------------
blander's Profile: http://www.excelforum.com/member.php...o&userid=36486
View this thread: http://www.excelforum.com/showthread...hreadid=568945

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.
--
Kevin Backmann


"blander" wrote:


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


--
blander
------------------------------------------------------------------------
blander's Profile: http://www.excelforum.com/member.php...o&userid=36486
View this thread: http://www.excelforum.com/showthread...hreadid=568945

Thanks guys

Really appreciate your help!!


--
blander
------------------------------------------------------------------------
blander's Profile: http://www.excelforum.com/member.php...o&userid=36486
View this thread: http://www.excelforum.com/showthread...hreadid=568945

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


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


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

--
blander
------------------------------------------------------------------------
blander's Profile: http://www.excelforum.com/member.php...o&userid=36486
View this thread: http://www.excelforum.com/showthread...hreadid=568945