Excel Manipulation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 Tata>Text 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
 
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
 
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
 
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
 
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
 
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 special>values.
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

 
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.
 
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
 
In microsoft.public.excel.misc on Sun, 29 Jul 2007, MartinW
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
Click to expand...
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.
 
OK Martin, I'll try to past the spreadsheet right here but you might have to
paste it into a larger area to see it spread out. Thanks. Let me know.

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)
 
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:

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)
 
Hi Baffled,

It looks like you are putting the B1: and C1: bits into the formulae
they are only there to explain it in the post. Perhaps this may make
it clearer.

Put this in cell B1
=MID(A1,1,FIND(" ",A1)-1)

Put this in cell C1
=MID(A1,FIND(" ",A1)+1,50)

As you drag the formulae down the sheet the A1 references should
update to A2, A3, A4 etc.
Don't double click on the fill handle at the moment just click and
drag it down 5 or 10 rows for now just to make sure it is working.

Once you get that bit working pull up and repost a sample
of your data from column A only. (Say A1 to A20)

It looks like you have 2 names separated by an & in some
of your cells so they will have to be addressed first.

Make sure your sample data contains any other variances there may be.

If you are still having trouble post your sheet to me at
floyd107<AT>bigpond<DOT>com (replace the at and dot as per usual)

Regards
Martin
 
You hit the nail on the head Martin. That is exactly what I was doing.
It is now working........BUT.........after I save it I try to delete Column A
and something like #REF appears in Columns B & C and all the data
disappears. What are your thoughts on this glitch.

Thank you very much.
 
Glad we got there. For the #REF you need to look back
to my original post.

It says,
Select all col B and col C data
Right click and copy, then right click again and paste values.
Then delete col A.
Click to expand...

This process converts your formulae back to text data.

If you delete column A before that step the formulae
in B and C are referencing a cell that is no longer there
thus the #REF error.

Regards
Martin
 
Baffled said:
OK Martin, I'll try to past the spreadsheet right here but you might have to
paste it into a larger area to see it spread out. Thanks. Let me know.

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("
Click to expand...

Baffled: when the OP asked you to post sample data, I don't know that
he had in mind to post a long list of unique names of business customers.
That, to me, seems to be a breach of ethics, if not laws.

I know if I were a customer and my name suddenly appeared on the web
on your partial list of customers, I'd be pretty exercised. This seems
to me to be confidential data.
 
Back
Top