Text to Columns - Only want to split SPECIFIC NUMBER of times

I am trying to split names across columns. The problem is that some names
spilt into 3 columns (first, middle, last), and others split across 5 or 6
(extra names, etc.)

Is there a way to specify split, using SPACE as a delimiter, but only split
on the first TWO spaces, then leave the rest alone?

I'm having a terrible time figuring this out. Any ideas anyone?

Thank you so much!

Mary

Hi,

Try using

=MID(A1,1,FIND(",",A1)-1)&" "&MID(A1,FIND("
",A1)+1,(FIND("!",SUBSTITUTE(A1,",","!",2))-FIND(" ",A1)-1))

where the names are in A1. This would give the 'firstname last name'
format in one single cell.

Another alternative is to use SPACE as delimiter, but select the rest of
the names and choose 'DO NOT IMPORT' in the Text to columns window.

Regards

Govind.

NewKid wrote:
> I am trying to split names across columns. The problem is that some names
> spilt into 3 columns (first, middle, last), and others split across 5 or 6
> (extra names, etc.)
>
> Is there a way to specify split, using SPACE as a delimiter, but only split
> on the first TWO spaces, then leave the rest alone?
>
> I'm having a terrible time figuring this out. Any ideas anyone?
>
> Thank you so much!
>
> Mary

Hi,

You may want to try the following array formula (Ctrl+Shift+Enter)

Assuming the name in cell A32, enter the following array formulas

In cell B32 (to get first name)

=MID(A32,MATCH(TRUE,EXACT(MID(A32,ROW($1:$40),1),PROPER(MID(A32,ROW($1:$40),1))),0),MATCH(TRUE,EXACT(MID(A32,ROW($2:$40),1),PROPER(MID(A32,ROW($2:$40),1))),0))

In cell C32 (to get middle name)

=IF(ISERROR(MID(A32,SEARCH(" ",A32,1)+1,SEARCH("
",A32,MATCH(32,CODE(MID(A32,ROW($1:$40),1)),0)+1)-SEARCH(" ",A32,1))),"
",MID(A32,SEARCH(" ",A32,1)+1,SEARCH("
",A32,MATCH(32,CODE(MID(A32,ROW($1:$40),1)),0)+1)-SEARCH(" ",A32,1)))

In cell D32, to get anything after that

=IF(ISERROR(MID(A32,SEARCH("
",A32,MATCH(32,CODE(MID(A32,ROW($1:$40),1)),0)+1)+1,255)),MID(A32,MATCH(TRUE,EXACT(MID(A32,ROW($2:$40),1),PROPER(MID(A32,ROW($2:$40),1))),0)+2,255),MID(A32,SEARCH(" ",A32,MATCH(32,CODE(MID(A32,ROW($1:$40),1)),0)+1)+1,255))

Regards,

Ashish Mathur



"NewKid" wrote:

> I am trying to split names across columns. The problem is that some names
> spilt into 3 columns (first, middle, last), and others split across 5 or 6
> (extra names, etc.)
>
> Is there a way to specify split, using SPACE as a delimiter, but only split
> on the first TWO spaces, then leave the rest alone?
>
> I'm having a terrible time figuring this out. Any ideas anyone?
>
> Thank you so much!
>
> Mary

Thank you. I've tried this, but still having some difficulty. As an example,
If my names are in one cell, such as this:

PATRICE D ALSTON
KIM SCHULTS
MARCY CERVENANSKY
QUINCEY WILSON
RICARDO L DE LA ROSA JR
SABRINA A WATSON SR
MILLISA N AZILLE
FABIOLA M OCHOA
TOI FIELDS
GREGORY MEJIA

And I want to split them up into columns using a SPACE as the delimiter,
BUT, I really only want the first name in a column, the middle name as a
column, and then all the rest as a column. I realize that I may still have
some manual cleanup to do, but on a large set of data, it would be helpful to
split them up like this.

When I tried your suggestion, I got this:

PATRICE D ALSTON P #VALUE! ALSTON
KIM SCHULTS K M SCHULTS
MARCY CERVENANSKY M RCY CERVENANSKY
QUINCEY WILSON Q INCEY WILSON
RICARDO L DE LA ROSA JR R #VALUE! DE LA ROSA JR
SABRINA A WATSON SR S #VALUE! A WATSON SR
MILLISA N AZILLE M #VALUE! AZILLE
FABIOLA M OCHOA F #VALUE! OCHOA
TOI FIELDS T I FIELDS

Any other ideas? Thanks so much.

NewKid


"Ashish Mathur" wrote:

> Hi,
>
> You may want to try the following array formula (Ctrl+Shift+Enter)
>
> Assuming the name in cell A32, enter the following array formulas
>
> In cell B32 (to get first name)
>
> =MID(A32,MATCH(TRUE,EXACT(MID(A32,ROW($1:$40),1),PROPER(MID(A32,ROW($1:$40),1))),0),MATCH(TRUE,EXACT(MID(A32,ROW($2:$40),1),PROPER(MID(A32,ROW($2:$40),1))),0))
>
> In cell C32 (to get middle name)
>
> =IF(ISERROR(MID(A32,SEARCH(" ",A32,1)+1,SEARCH("
> ",A32,MATCH(32,CODE(MID(A32,ROW($1:$40),1)),0)+1)-SEARCH(" ",A32,1))),"
> ",MID(A32,SEARCH(" ",A32,1)+1,SEARCH("
> ",A32,MATCH(32,CODE(MID(A32,ROW($1:$40),1)),0)+1)-SEARCH(" ",A32,1)))
>
> In cell D32, to get anything after that
>
> =IF(ISERROR(MID(A32,SEARCH("
> ",A32,MATCH(32,CODE(MID(A32,ROW($1:$40),1)),0)+1)+1,255)),MID(A32,MATCH(TRUE,EXACT(MID(A32,ROW($2:$40),1),PROPER(MID(A32,ROW($2:$40),1))),0)+2,255),MID(A32,SEARCH(" ",A32,MATCH(32,CODE(MID(A32,ROW($1:$40),1)),0)+1)+1,255))
>
> Regards,
>
> Ashish Mathur
>
>
>
> "NewKid" wrote:
>
> > I am trying to split names across columns. The problem is that some names
> > spilt into 3 columns (first, middle, last), and others split across 5 or 6
> > (extra names, etc.)
> >
> > Is there a way to specify split, using SPACE as a delimiter, but only split
> > on the first TWO spaces, then leave the rest alone?
> >
> > I'm having a terrible time figuring this out. Any ideas anyone?
> >
> > Thank you so much!
> >
> > Mary

On Thu, 26 Jan 2006 15:22:02 -0800, NewKid <[email protected]>
wrote:

>I am trying to split names across columns. The problem is that some names
>spilt into 3 columns (first, middle, last), and others split across 5 or 6
>(extra names, etc.)
>
>Is there a way to specify split, using SPACE as a delimiter, but only split
>on the first TWO spaces, then leave the rest alone?
>
>I'm having a terrible time figuring this out. Any ideas anyone?
>
>Thank you so much!
>
>Mary

1. Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then use these formulas:

Assume Name String is in A1:

B1: =REGEX.MID($A1,"\w+",1)
C1: =REGEX.MID($A1,"\w+",2)
D1: =REGEX.SUBSTITUTE(TRIM(A1),"^\w+(\s|$)(\w+(\s|$))?")

These are "regular expressions"

B1: Get first word
C1: Get 2nd word
D1: Removes 1st and (if present) 2nd words; so returns the rest
--ron

Hello Ron,
Thank you for your help. The "morefunc.xll" functions are very useful. I
appreciate this. Thanks again.
Mary



"Ron Rosenfeld" wrote:

> On Thu, 26 Jan 2006 15:22:02 -0800, NewKid <[email protected]>
> wrote:
>
> >I am trying to split names across columns. The problem is that some names
> >spilt into 3 columns (first, middle, last), and others split across 5 or 6
> >(extra names, etc.)
> >
> >Is there a way to specify split, using SPACE as a delimiter, but only split
> >on the first TWO spaces, then leave the rest alone?
> >
> >I'm having a terrible time figuring this out. Any ideas anyone?
> >
> >Thank you so much!
> >
> >Mary
>
> 1. Download and install Longre's free morefunc.xll add-in from
> http://xcell05.free.fr/
>
> Then use these formulas:
>
> Assume Name String is in A1:
>
> B1: =REGEX.MID($A1,"\w+",1)
> C1: =REGEX.MID($A1,"\w+",2)
> D1: =REGEX.SUBSTITUTE(TRIM(A1),"^\w+(\s|$)(\w+(\s|$))?")
>
> These are "regular expressions"
>
> B1: Get first word
> C1: Get 2nd word
> D1: Removes 1st and (if present) 2nd words; so returns the rest
> --ron
>

On Mon, 30 Jan 2006 10:57:28 -0800, NewKid <[email protected]>
wrote:

>Hello Ron,
>Thank you for your help. The "morefunc.xll" functions are very useful. I
>appreciate this. Thanks again.
>Mary
>
>
>

Glad to help. And yes, Laurent has a lot of good stuff in that add-in.


--ron

On Mon, 30 Jan 2006 10:57:28 -0800, NewKid <[email protected]>
wrote:

>Hello Ron,
>Thank you for your help. The "morefunc.xll" functions are very useful. I
>appreciate this. Thanks again.
>Mary

Mary,

Thinking about possibilities, the expression I gave you (for the third column)
has a problem if anything in the first two words contains a dot (e.g. Millisa
N. Azille). So this will correct that oversight:

=REGEX.SUBSTITUTE(TRIM($A1),"^(\w+\.?(\s|$)){1,2}")


--ron

You are wonderful. Thanks!!!!

"Ron Rosenfeld" wrote:

> On Mon, 30 Jan 2006 10:57:28 -0800, NewKid <[email protected]>
> wrote:
>
> >Hello Ron,
> >Thank you for your help. The "morefunc.xll" functions are very useful. I
> >appreciate this. Thanks again.
> >Mary
>
> Mary,
>
> Thinking about possibilities, the expression I gave you (for the third column)
> has a problem if anything in the first two words contains a dot (e.g. Millisa
> N. Azille). So this will correct that oversight:
>
> =REGEX.SUBSTITUTE(TRIM($A1),"^(\w+\.?(\s|$)){1,2}")
>
>
> --ron
>

It looks like you have an Excel answer on this, but I do a workaround. I copy
the columns I want to split out to Word, convert the table to text, then
replace spaces with tabs. Finally, I convert the resulting text back to
table, copy, and paste back in Excel.