Text Functions,
 

Hello, Can someone help me solve this problem. I have a mailing list
consisting of single cell names (first <could be initials, Middle, Last, and
a sufix <Sr. Jr. IV, etc.). What I have been trying to do is to separate
the names into two columns,

A (Separate to) B
c

A1 A Francisco Madrigal A Francisco Madrigal
A2 A J Benning A J Benning
A3 Aaron Isaac Laine Aaron Isaac Laine
A4 Abraham A. Abreu Sr Abraham A. Abreu Sr

I have tried making several formulas but so far I have not been able to get
the separated into the two columms.

--
Lowell Shoaf
Realtor
RE/MAX Gulfstream Realty
Sarasota, Florida

You can try =Left(x,x) and =right. These split out the left hand or right
hand characters. You can try different combos of these to get the columns
split. A bit of manual tidying may be required to finish off.
--
Peter
London, UK


"Lowell" wrote:

Hello, Can someone help me solve this problem. I have a mailing list
consisting of single cell names (first <could be initials, Middle, Last, and
a sufix <Sr. Jr. IV, etc.). What I have been trying to do is to separate
the names into two columns,

A (Separate to) B
c

A1 A Francisco Madrigal A Francisco Madrigal
A2 A J Benning A J Benning
A3 Aaron Isaac Laine Aaron Isaac Laine
A4 Abraham A. Abreu Sr Abraham A. Abreu Sr

I have tried making several formulas but so far I have not been able to get
the separated into the two columms.

--
Lowell Shoaf
Realtor
RE/MAX Gulfstream Realty
Sarasota, Florida

Thanks for the advise, I tried what you suggested and I cannot get it to work.


--
Lowell Shoaf
Realtor
RE/MAX Gulfstream Realty
Sarasota, Florida


"Peter" wrote:

You can try =Left(x,x) and =right. These split out the left hand or right
hand characters. You can try different combos of these to get the columns
split. A bit of manual tidying may be required to finish off.
--
Peter
London, UK


"Lowell" wrote:

Hello, Can someone help me solve this problem. I have a mailing list
consisting of single cell names (first <could be initials, Middle, Last, and
a sufix <Sr. Jr. IV, etc.). What I have been trying to do is to separate
the names into two columns,

A (Separate to) B
c

A1 A Francisco Madrigal A Francisco Madrigal
A2 A J Benning A J Benning
A3 Aaron Isaac Laine Aaron Isaac Laine
A4 Abraham A. Abreu Sr Abraham A. Abreu Sr

I have tried making several formulas but so far I have not been able to get
the separated into the two columms.

--
Lowell Shoaf
Realtor
RE/MAX Gulfstream Realty
Sarasota, Florida

Hi,

Try this.

If the name is entered in cell A6, then enter the following array formula
(Ctrl+Shift+Enter) in cell B6 to get the first name

MID(A6,MATCH(TRUE,EXACT(MID(A6,ROW($1:$14),1),PROP ER(MID(A6,ROW($1:$14),1))),0),MATCH(TRUE,EXACT(MID (A6,ROW($2:$14),1),PROPER(MID(A6,ROW($2:$14),1))), 0))

Enter the following array formula (Ctrl+Shift+Enter) in cell C6 to get the
second and last name

MID(A6,MATCH(TRUE,EXACT(MID(A6,ROW($2:$14),1),PROP ER(MID(A6,ROW($2:$14),1))),0)+1,255)

Regards,

Ashish Mathur





"Lowell" wrote:

Hello, Can someone help me solve this problem. I have a mailing list
consisting of single cell names (first <could be initials, Middle, Last, and
a sufix <Sr. Jr. IV, etc.). What I have been trying to do is to separate
the names into two columns,

A (Separate to) B
c

A1 A Francisco Madrigal A Francisco Madrigal
A2 A J Benning A J Benning
A3 Aaron Isaac Laine Aaron Isaac Laine
A4 Abraham A. Abreu Sr Abraham A. Abreu Sr

I have tried making several formulas but so far I have not been able to get
the separated into the two columms.

--
Lowell Shoaf
Realtor
RE/MAX Gulfstream Realty
Sarasota, Florida

Hi!

Those formulas are just separating everything to the left of the first space
and everything to the right of the first space. These formulas will do the
same thing:

=LEFT(A6,FIND(" ",A6)-1)

=MID(A6,FIND(" ",A6)+1,255)

There's really no good way to do this! Too many variables to consider.

Billy Bob Michael Van Patton Sr.
Bill Gates

Where does the first name end and the middle name begin in the first
example? Where does the middle name end and the last name begin? It's easy
to "see" but try writing a formula to do it and cover all the possibilities.

Biff

"Ashish Mathur" wrote in message
...
Hi,

Try this.

If the name is entered in cell A6, then enter the following array formula
(Ctrl+Shift+Enter) in cell B6 to get the first name

MID(A6,MATCH(TRUE,EXACT(MID(A6,ROW($1:$14),1),PROP ER(MID(A6,ROW($1:$14),1))),0),MATCH(TRUE,EXACT(MID (A6,ROW($2:$14),1),PROPER(MID(A6,ROW($2:$14),1))), 0))

Enter the following array formula (Ctrl+Shift+Enter) in cell C6 to get the
second and last name

MID(A6,MATCH(TRUE,EXACT(MID(A6,ROW($2:$14),1),PROP ER(MID(A6,ROW($2:$14),1))),0)+1,255)

Regards,

Ashish Mathur





"Lowell" wrote:

Hello, Can someone help me solve this problem. I have a mailing list
consisting of single cell names (first <could be initials, Middle, Last,
and
a sufix <Sr. Jr. IV, etc.). What I have been trying to do is to
separate
the names into two columns,

A (Separate to) B
c

A1 A Francisco Madrigal A Francisco Madrigal
A2 A J Benning A J
Benning
A3 Aaron Isaac Laine Aaron Isaac Laine
A4 Abraham A. Abreu Sr Abraham A. Abreu Sr

I have tried making several formulas but so far I have not been able to
get
the separated into the two columms.

--
Lowell Shoaf
Realtor
RE/MAX Gulfstream Realty
Sarasota, Florida

Happen to come across this today...
http://www.add-ins.com/name_splitter.htm
Is it what you're looking for?

--
Peter
London, UK


"Peter" wrote:

You can try =Left(x,x) and =right. These split out the left hand or right
hand characters. You can try different combos of these to get the columns
split. A bit of manual tidying may be required to finish off.
--
Peter
London, UK


"Lowell" wrote:

Hello, Can someone help me solve this problem. I have a mailing list
consisting of single cell names (first <could be initials, Middle, Last, and
a sufix <Sr. Jr. IV, etc.). What I have been trying to do is to separate
the names into two columns,

A (Separate to) B
c

A1 A Francisco Madrigal A Francisco Madrigal
A2 A J Benning A J Benning
A3 Aaron Isaac Laine Aaron Isaac Laine
A4 Abraham A. Abreu Sr Abraham A. Abreu Sr

I have tried making several formulas but so far I have not been able to get
the separated into the two columms.

--
Lowell Shoaf
Realtor
RE/MAX Gulfstream Realty
Sarasota, Florida

Happen to come across this today...
http://www.add-ins.com/name_splitter.htm
Is it what you're looking for?

--
Peter
London, UK

--
Peter
London, UK


"Lowell" wrote:

Hello, Can someone help me solve this problem. I have a mailing list
consisting of single cell names (first <could be initials, Middle, Last, and
a sufix <Sr. Jr. IV, etc.). What I have been trying to do is to separate
the names into two columns,

A (Separate to) B
c

A1 A Francisco Madrigal A Francisco Madrigal
A2 A J Benning A J Benning
A3 Aaron Isaac Laine Aaron Isaac Laine
A4 Abraham A. Abreu Sr Abraham A. Abreu Sr

I have tried making several formulas but so far I have not been able to get
the separated into the two columms.

--
Lowell Shoaf
Realtor
RE/MAX Gulfstream Realty
Sarasota, Florida

Peter,

Thanks. This is exactly what I am looking for. What part of London are you
from? my wife and I used to live in Chelmsford.

Regards,

Lowell
--
Lowell Shoaf
Realtor
RE/MAX Gulfstream Realty
Sarasota, Florida


"Peter" wrote:

Happen to come across this today...
http://www.add-ins.com/name_splitter.htm
Is it what you're looking for?

--
Peter
London, UK

--
Peter
London, UK


"Lowell" wrote:

Hello, Can someone help me solve this problem. I have a mailing list
consisting of single cell names (first <could be initials, Middle, Last, and
a sufix <Sr. Jr. IV, etc.). What I have been trying to do is to separate
the names into two columns,

A (Separate to) B
c

A1 A Francisco Madrigal A Francisco Madrigal
A2 A J Benning A J Benning
A3 Aaron Isaac Laine Aaron Isaac Laine
A4 Abraham A. Abreu Sr Abraham A. Abreu Sr

I have tried making several formulas but so far I have not been able to get
the separated into the two columms.

--
Lowell Shoaf
Realtor
RE/MAX Gulfstream Realty
Sarasota, Florida

The other side of town - Ealing. Glad to have helped. Nice to work with you!
--
Peter
London, UK


"Lowell" wrote:

Peter,

Thanks. This is exactly what I am looking for. What part of London are you
from? my wife and I used to live in Chelmsford.

Regards,

Lowell
--
Lowell Shoaf
Realtor
RE/MAX Gulfstream Realty
Sarasota, Florida


"Peter" wrote:

Happen to come across this today...
http://www.add-ins.com/name_splitter.htm
Is it what you're looking for?

--
Peter
London, UK

--
Peter
London, UK


"Lowell" wrote:

Hello, Can someone help me solve this problem. I have a mailing list
consisting of single cell names (first <could be initials, Middle, Last, and
a sufix <Sr. Jr. IV, etc.). What I have been trying to do is to separate
the names into two columns,

A (Separate to) B
c

A1 A Francisco Madrigal A Francisco Madrigal
A2 A J Benning A J Benning
A3 Aaron Isaac Laine Aaron Isaac Laine
A4 Abraham A. Abreu Sr Abraham A. Abreu Sr

I have tried making several formulas but so far I have not been able to get
the separated into the two columms.

--
Lowell Shoaf
Realtor
RE/MAX Gulfstream Realty
Sarasota, Florida