Splitting data into two columns

[Guest]

I have data I want split into two columns. It currently is first name, last
name in one column. I'd like to make two columns out of it, separate first
name and last name. There is a space between the first and last name. How
can I split this column into two?

Thanks bunches

Carolyn

 

[RagDyeR]

<Data> <TextToColumns>
Check "Delimited", then <Next>
Check "Space", then <Finish>.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I have data I want split into two columns. It currently is first name, last
name in one column. I'd like to make two columns out of it, separate first
name and last name. There is a space between the first and last name. How
can I split this column into two?

Thanks bunches

Carolyn

 

[Guest]

That didn't work for me. There was no creation of the data in two columns!
I don't understand. It looks like it should work

 

[RagDyeR]

Did you select your entire list before you started?

 

[Guest]

Yes, and I even cut a portion of the list out and worked on that piece alone
too, playing with changing both the consective delimiters and final location.
It doesn't appear to do anything but just repaste the existing data into one
new column. I'm perplexed and frustrated. Any new ideas, I really
appreciate your assistance.

 

[Guest]

Are you on messenger? Perhaps I could send the file to you to look at and
see if there is something strange about my file that would prevent it from
splitting into two columns?

 

[RagDyeR]

From your description, I would guess that you imported this list of names,
and the spaces between the names are "non-breaking" spaces.

When you go to the second window of TTC, under "DataPreview", WYSIWYG.
Are the names separated in this window by a black line?

Try this:

Copy some of these names to a column, and you key in some names yourself,
using the normal space bar.
Now, when you go to the second window of TTC, scroll up and/or down, and see
if you can tell the difference between what you entered and what your
original data looked like.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Are you on messenger? Perhaps I could send the file to you to look at and
see if there is something strange about my file that would prevent it from
splitting into two columns?

 

[Guest]

Yes!! It was imported and the DataPreview doesn't show it broken. Can we
fix that?? Can we change non-breaking spaces to what they need to be?

 

[Aladin Akyurek]

Probably a case for the TrimAll macro!

From your description, I would guess that you imported this list of names,
and the spaces between the names are "non-breaking" spaces.

When you go to the second window of TTC, under "DataPreview", WYSIWYG.
Are the names separated in this window by a black line?

Try this:

Copy some of these names to a column, and you key in some names yourself,
using the normal space bar.
Now, when you go to the second window of TTC, scroll up and/or down, and
see
if you can tell the difference between what you entered and what your
original data looked like.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Are you on messenger? Perhaps I could send the file to you to look at and
see if there is something strange about my file that would prevent it from
splitting into two columns?

 

[RagDyeR]

Don't have to do that.
Just tell TTC what to look for to complete the separation.

I'm assuming that they *are* "non-breaking" spaces, and are therefore
CHAR(160).

So, in the second window, in addition to checking "spaces", also check
"other", and enter this in the "other" box, even though you will *not* see
anything displayed when you're finished:

<Alt> 0160

Where you *must* use the numbers from the num keypad, *not* the numbers
under the function keys.

Now check out your display and see if it's OK.

I'm just guessing at the possibility that the space you're seeing is
CHAR(160).
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Yes!! It was imported and the DataPreview doesn't show it broken. Can we
fix that?? Can we change non-breaking spaces to what they need to be?

 

[Guest]

IT WORKED!!! Thank you, Thank you, Thank you!! You are fantastic!!!!!

 

[RagDyeR]

Thanks for the feed-back.

 

[Guest]

Hi,

Try the following array formula (Ctrl+Shift+Enter) for obtaining the first
name

MID(A16,MATCH(TRUE,EXACT(MID(A16,ROW($1:$13),1),PROPER(MID(A16,ROW($1:$13),1))),0),MATCH(TRUE,EXACT(MID(A16,ROW($2:$13),1),PROPER(MID(A16,ROW($2:$13),1))),0))

For obtaining the last name, try the following array formula
(Ctrl+Shift+Enter)

MID(A16,MATCH(TRUE,EXACT(MID(A16,ROW($2:$13),1),PROPER(MID(A16,ROW($2:$13),1))),0)+2,255)

Regards,

Ashish Mathur