I have two columns on my excel sheet (13000 records)

Column A is: John Smith
I want to put the John in Column A and Smith in Column B

Column B is: 1240 Main Street
I want to put 1240 in Column C and Main Street in Column D

Do-able?

Hi

It's easy until all words (strings separated with a single space) must be in
different columns.
P.e. insert enough empty columns after column A, select the column (or a
range there), and then from menu TataText to Columns..., and proceed with
wizard.

For your 2 example, it would be trickier. When the number of different
multi-word street names is limited, you may use ReplaceAll to replace 'Main
Street' p.e. with 'Main_Street' everywhere in colum, and repeat the process
for all strees alike. After that you can apply TextToColumns operation, and
then ReplaceAll to replace all "_" with single space.


Arvi Laanemets


"Baffled" wrote in message
...
I have two columns on my excel sheet (13000 records)

Column A is: John Smith
I want to put the John in Column A and Smith in Column B

Column B is: 1240 Main Street
I want to put 1240 in Column C and Main Street in Column D

Do-able?

Hi Baffled,

If you have to deal with multiple surnames it may be better to
do it like this.
With your names in column A.
B1: =MID(A1,1,FIND(" ",A1)-1)
C1: =MID(A1,FIND(" ",A1)+1,50)
Drag both formulae down to the end of your data.
Select all col B and col C data
Right click and copy, then right click again and paste values.
Then delete col A.
Repeat the process for the address part.

The formula in B1 will return anything to the left of the first space
The formula in C1 will return anything to the right of the first space

HTH
Martin

"Baffled" wrote in message
...
I have two columns on my excel sheet (13000 records)

Column A is: John Smith
I want to put the John in Column A and Smith in Column B

Column B is: 1240 Main Street
I want to put 1240 in Column C and Main Street in Column D

Do-able?

Hi

There may be cases, where people have either several forenames or several
surnames, or several of both. So do do it with formulas or using some
universal procedure is possible only for special cases.


Arvi Laanemets


"MartinW" wrote in message
...
Hi Baffled,

If you have to deal with multiple surnames it may be better to
do it like this.
With your names in column A.
B1: =MID(A1,1,FIND(" ",A1)-1)
C1: =MID(A1,FIND(" ",A1)+1,50)
Drag both formulae down to the end of your data.
Select all col B and col C data
Right click and copy, then right click again and paste values.
Then delete col A.
Repeat the process for the address part.

The formula in B1 will return anything to the left of the first space
The formula in C1 will return anything to the right of the first space

HTH
Martin

"Baffled" wrote in message
...
I have two columns on my excel sheet (13000 records)

Column A is: John Smith
I want to put the John in Column A and Smith in Column B

Column B is: 1240 Main Street
I want to put 1240 in Column C and Main Street in Column D

Do-able?

Yes Arvi,

That's why I put the explanation at the end of my thread.
Perhaps I should have emphasised it more. Something like...

The formula in B1 will return anything to the LEFT of the FIRST space only.
The formula in C1 will return anything to the RIGHT of the FIRST space only.

Regards
Martin


"Arvi Laanemets" wrote in message
...
Hi

There may be cases, where people have either several forenames or several
surnames, or several of both. So do do it with formulas or using some
universal procedure is possible only for special cases.


Arvi Laanemets


"MartinW" wrote in message
...
Hi Baffled,

If you have to deal with multiple surnames it may be better to
do it like this.
With your names in column A.
B1: =MID(A1,1,FIND(" ",A1)-1)
C1: =MID(A1,FIND(" ",A1)+1,50)
Drag both formulae down to the end of your data.
Select all col B and col C data
Right click and copy, then right click again and paste values.
Then delete col A.
Repeat the process for the address part.

The formula in B1 will return anything to the left of the first space
The formula in C1 will return anything to the right of the first space

HTH
Martin

"Baffled" wrote in message
...
I have two columns on my excel sheet (13000 records)

Column A is: John Smith
I want to put the John in Column A and Smith in Column B

Column B is: 1240 Main Street
I want to put 1240 in Column C and Main Street in Column D

Do-able?

Hi Martin:

Nothing is working right.

Do I put spaces anywhere within either of your formulas?
Do I create an empty column B to the right of A?
Do I create an empty column D to the right of B?
Do I insert one formula in column E and the other formulat in column F?
Do I click on Edit, Copy, Paste Special, Forumlas, OK?

Thanks

"MartinW" wrote:

Hi Baffled,

If you have to deal with multiple surnames it may be better to
do it like this.
With your names in column A.
B1: =MID(A1,1,FIND(" ",A1)-1)
C1: =MID(A1,FIND(" ",A1)+1,50)
Drag both formulae down to the end of your data.
Select all col B and col C data
Right click and copy, then right click again and paste values.
Then delete col A.
Repeat the process for the address part.

The formula in B1 will return anything to the left of the first space
The formula in C1 will return anything to the right of the first space

HTH
Martin

"Baffled" wrote in message
...
I have two columns on my excel sheet (13000 records)

Column A is: John Smith
I want to put the John in Column A and Smith in Column B

Column B is: 1240 Main Street
I want to put 1240 in Column C and Main Street in Column D

Do-able?

Hi Baffled,

Starting with your data in Column A and coloumn B
Insert two new empty columns B and C (your B data will now be in col D)
Copy these formulae in.
B1: =MID(A1,1,FIND(" ",A1)-1)
C1: =MID(A1,FIND(" ",A1)+1,50)
Drag both formulae down to the end of your data.
Select all col B and col C data
Right click and copy,
then right click again and paste specialvalues.
Then delete col A.

You should now have your column A data split into column A
and column B (with your original column B data now in column C)

Repeat the process on your new column C (which is your address data)

Also take into account that this process won't work if your data has
multiple first names or other such problems. If that is the case you
will need to repost your data showing a better example of all the
differences
that exist in your 13000 rows.

HTH
Martin


Hi Baffled,

If you have to deal with multiple surnames it may be better to
do it like this.
With your names in column A.
B1: =MID(A1,1,FIND(" ",A1)-1)
C1: =MID(A1,FIND(" ",A1)+1,50)
Drag both formulae down to the end of your data.
Select all col B and col C data
Right click and copy, then right click again and paste values.
Then delete col A.
Repeat the process for the address part.

The formula in B1 will return anything to the left of the first space
The formula in C1 will return anything to the right of the first space

HTH
Martin

"Baffled" wrote in message
...
I have two columns on my excel sheet (13000 records)

Column A is: John Smith
I want to put the John in Column A and Smith in Column B

Column B is: 1240 Main Street
I want to put 1240 in Column C and Main Street in Column D

Do-able?

Do you mind if I email you a copy of a small sample of the data base so you
can see what I am doing? I believe I am following your advise precisely.
But it is not working.

"MartinW" wrote:

Hi Baffled,

Starting with your data in Column A and coloumn B
Insert two new empty columns B and C (your B data will now be in col D)
Copy these formulae in.
B1: =MID(A1,1,FIND(" ",A1)-1)
C1: =MID(A1,FIND(" ",A1)+1,50)
Drag both formulae down to the end of your data.
Select all col B and col C data
Right click and copy,
then right click again and paste specialvalues.
Then delete col A.

You should now have your column A data split into column A
and column B (with your original column B data now in column C)

Repeat the process on your new column C (which is your address data)

Also take into account that this process won't work if your data has
multiple first names or other such problems. If that is the case you
will need to repost your data showing a better example of all the
differences
that exist in your 13000 rows.

HTH
Martin


Hi Baffled,

If you have to deal with multiple surnames it may be better to
do it like this.
With your names in column A.
B1: =MID(A1,1,FIND(" ",A1)-1)
C1: =MID(A1,FIND(" ",A1)+1,50)
Drag both formulae down to the end of your data.
Select all col B and col C data
Right click and copy, then right click again and paste values.
Then delete col A.
Repeat the process for the address part.

The formula in B1 will return anything to the left of the first space
The formula in C1 will return anything to the right of the first space

HTH
Martin

"Baffled" wrote in message
...
I have two columns on my excel sheet (13000 records)

Column A is: John Smith
I want to put the John in Column A and Smith in Column B

Column B is: 1240 Main Street
I want to put 1240 in Column C and Main Street in Column D

Do-able?

Hi Baffled,

I prefer it if you post your data here on the groups so that everyone can
follow what's going on as well as contribute their ideas.

If you feel it would be better you can post it to
floyd107ATbigpondDOTcom (making the obvious changes)

I should warn that it is 2:00AM here and I am off to bed now
so it may be a while before I reply.

Regards
Martin


"Baffled" wrote in message
...

Do you mind if I email you a copy of a small sample of the data base so
you
can see what I am doing? I believe I am following your advise precisely.
But it is not working.

"MartinW" wrote:

Hi Baffled,

Starting with your data in Column A and coloumn B
Insert two new empty columns B and C (your B data will now be in col D)
Copy these formulae in.
B1: =MID(A1,1,FIND(" ",A1)-1)
C1: =MID(A1,FIND(" ",A1)+1,50)
Drag both formulae down to the end of your data.
Select all col B and col C data
Right click and copy,
then right click again and paste specialvalues.
Then delete col A.

You should now have your column A data split into column A
and column B (with your original column B data now in column C)

Repeat the process on your new column C (which is your address data)

Also take into account that this process won't work if your data has
multiple first names or other such problems. If that is the case you
will need to repost your data showing a better example of all the
differences
that exist in your 13000 rows.

HTH
Martin


Hi Baffled,

If you have to deal with multiple surnames it may be better to
do it like this.
With your names in column A.
B1: =MID(A1,1,FIND(" ",A1)-1)
C1: =MID(A1,FIND(" ",A1)+1,50)
Drag both formulae down to the end of your data.
Select all col B and col C data
Right click and copy, then right click again and paste values.
Then delete col A.
Repeat the process for the address part.

The formula in B1 will return anything to the left of the first space
The formula in C1 will return anything to the right of the first space

HTH
Martin

"Baffled" wrote in message
...
I have two columns on my excel sheet (13000 records)

Column A is: John Smith
I want to put the John in Column A and Smith in Column B

Column B is: 1240 Main Street
I want to put 1240 in Column C and Main Street in Column D

Do-able?

In microsoft.public.excel.misc on Sun, 29 Jul 2007, MartinW
wrote :
Hi Baffled,

If you have to deal with multiple surnames it may be better to
do it like this.
With your names in column A.
B1: =MID(A1,1,FIND(" ",A1)-1)
C1: =MID(A1,FIND(" ",A1)+1,50)
Drag both formulae down to the end of your data.
Select all col B and col C data
Right click and copy, then right click again and paste values.
Then delete col A.
Repeat the process for the address part.

The formula in B1 will return anything to the left of the first space
The formula in C1 will return anything to the right of the first space

That's a very useful pair of formulas - I often have to split text, but
never would have thought of doing it like that.

Thanks.
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

Thank you Paul. I'll give this a whirl. But please see my previous response
to Martin. Somehow I'm doing something wrong because, when I double click on
the little box in the lower right hand corner to copy the formula down,
subsequent action (EDIT, COPY etc) is producing no results.

"Paul Hyett" wrote:

In microsoft.public.excel.misc on Sun, 29 Jul 2007, MartinW
wrote :
Hi Baffled,

If you have to deal with multiple surnames it may be better to
do it like this.
With your names in column A.
B1: =MID(A1,1,FIND(" ",A1)-1)
C1: =MID(A1,FIND(" ",A1)+1,50)
Drag both formulae down to the end of your data.
Select all col B and col C data
Right click and copy, then right click again and paste values.
Then delete col A.
Repeat the process for the address part.

The formula in B1 will return anything to the left of the first space
The formula in C1 will return anything to the right of the first space

That's a very useful pair of formulas - I often have to split text, but
never would have thought of doing it like that.

Thanks.
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

Paul:

Just tried your idea but it is not working. I copy, but the past is not
causing ANY action. The data just sits there in copy mode with the line
moving around Column B and C indicating it is ready to be copied.

Here's a paste:

Customer B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
USPE SONIA B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
USAIDI L & OPEMAN S B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
USAR N. B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
USARCA ANGELA B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
USARCA ELEUTERIO B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
USARCA MARIO B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
USARCA SUSANNA B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
USATE C. B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
USBASI MOHAMED B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
USBOTT MARGARET B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
USDUL JOAN B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
USEL M & OVERMYER A B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
USELDANO MARIA B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
USIC ZORKA B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
USITIA MANUEL B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
USLUTON EVELYN B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
USMA THOS E. B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
USOUASSAF OUDEI B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
USOYTES JUAN & ANA B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
USRMAAM DONOPD &
JULIE B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
USRMAAM J & MARTINEZ
L B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
USRMAAM R& L B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
USRMAAMSON LLOYD B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
USRAM & SON B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
USRANTES E & NEBLUNG
L B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
USREGO PEOPBERTO B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
USREGO OPICIA B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
USUASSAF MMAMOUD B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
USUKNDEIR AMANI B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
USURTO REYES B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
USUSHANUS MAZIN B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
USUTOPEB AYMAN B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
ILAPULCO MARISCOS B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
ILAPULCO MARISCOS B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
ILCAROO
MICHAEL/KRISTINE B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
ILEVEDO J & NAVA L B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
ILEVEDO MARGARITA B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
ILEVEDO PATTY B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
ILEY MEREDITH B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
ILHTERKIRCHEN K.F. B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
ILIERNO ANTHONY J. B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
ILOSTA OPVIN B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
ILOSTA CESILO &
RAQUEL B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
ILOSTA D & RIVERA G B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
ILOSTA E & POPPEINES
G B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
ILOSTA IVAN B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
ILOSTA KATHRYN B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
ILOSTA MARIA O. B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
ILOSTA R & C & A B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
ILOSTA RAUL B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
ILOSTA RICARDO &
KARLA B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
ILOSTA SANDRA E. B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
ILOSTA V/POSSI W/PEREZ
R B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
ILUNA REYNEL B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
PEPE WOPTER B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
PEMA USDOLLMA B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
PEAMAN SOPA B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
PEAMCZYK J P B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
PEAME FERNANDO B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
PEAME RICARDO B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
PEAMEC TILLIE B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
PEAMI OPBERT & LINDA B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
PEAMIEC DAVID & LUBA B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
PEAMONIS GINTARAS B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
PEAMS LOUIS B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
PEAN EMILIO B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
PEAN PUSLO/NAJERA
MARIA B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
PEDOPIA MRS J B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
PEDUCCI ANTHONY B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
PEDUCI DENNIS B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
PEKINS DEWEY B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
PEKINS TIMOTHY L B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
PEOLPH-B FUNEROP LTD B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
PEONO RAMON & ORTIZ
ROMAN B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
PEVANTJAE FINANCIOP
LLC B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAATE JOE B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAATE MARIANO B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAATE RENZO & MARGIE B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAIROS MARGARET B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAOCS L B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAOS STEVE B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAOZZINO ONOFRIO B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JARELA JOHN G. B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JARELA MARIA B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JARUSA PETER B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAUAYO DIGNAZAR B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAUAYO MARINA &
MONICA B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAUILAR ELIZUSETH B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAUILAR B & B B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAUILAR F & PPEILLA G B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAUILAR GREGORIO B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAUILAR JR R & P B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAUILAR L & M B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAUILAR LEON/DIAZ
SILVIA B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAUILAR MARIO B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAUILAR MARIO B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAUILAR MOISES B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAUILAR P B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAUILAR PIEDPE B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAUILAR RAMON/RIVERA
J B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAUILAR SOPVPEOR &
ANA B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAUILAR VICTORIA B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAUILERA ANTONIO B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAUILERA MIGUEL A B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAUILERA NOEL &
BERTHA B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAUILUZ E & AROCHO M B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAUINJAA RICARDO &
MARIA B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAUIRIE
JOHATHAN/JOHANA B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAUIRRE OPFONSO/MARIA B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAUIRRE DEBORMA B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAUIRRE ELIZUSETH B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAUIRRE GERARDO B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAUIRRE J & BARRIUSO
C B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAUIRRE JUAN & SANDRA B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAUIRRE JUANITA B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAUIRRE LINDA B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
JAUIRRE MAIRA B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
MAAMIRANO
MARELO/LOURDES B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
MAEARN MARK JAMES B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
MOPBORN MARY ANNE &
NOLAN B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
MAMED MJADY B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
MAMED MJADY A. B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
MARENS JOHN &
ROSEANNE B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
MAUMPEA RUTILIO B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
MAUMPEA RUTILIO &
MARIA B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
OPAIEH MMAD B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
OPAMO F & CARBAJOP G B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
OPAMOS RAMON B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
OPANIS JOSE & DEFRATES
E. B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
OPANIS MARK &
CAROLINA B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
OPANIS VICTOR B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
OPANIZ ARTURO B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
OPANIZ J. & CORDOVA
J. B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
OPANIZ M & HERRERA C B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
OPARCON MILCO A. B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
OPARCON PEDRO B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
OPAS RICARDO B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
OPAVARPEO I & RODRIQUEZ
M B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
OPBA EUGENIA B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
OPBA EVARISTO B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
OPBA FRANCISCO &
OLINDA B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
OPBA MJADOPENA B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
OPBA MANNUEL B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
OPBA MIGUEL B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
OPBA, FRANCISCO B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
OPBILHIARA STEVE B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
OPBANO DANTE & MAY B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
OPBAVERA AUREO B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
OPBEITER M & BUCZKO M B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
OPBERT A B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)
OPBERT, RAYMOND B1:=MID(A1,1,FIND("",A1)-1) C1:=MID(A1,FIND("",A1)+1,50)





"Paul Hyett" wrote:

In microsoft.public.excel.misc on Sun, 29 Jul 2007, MartinW
wrote :
Hi Baffled,

If you have to deal with multiple surnames it may be better to
do it like this.
With your names in column A.
B1: =MID(A1,1,FIND(" ",A1)-1)
C1: =MID(A1,FIND(" ",A1)+1,50)
Drag both formulae down to the end of your data.
Select all col B and col C data
Right click and copy, then right click again and paste values.
Then delete col A.
Repeat the process for the address part.

The formula in B1 will return anything to the left of the first space
The formula in C1 will return anything to the right of the first space

That's a very useful pair of formulas - I often have to split text, but
never would have thought of doing it like that.

Thanks.
--
Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)