Names in column are mixed up

  • Thread starter Thread starter saybut
  • Start date Start date
S

saybut

Hi,

I was wondering if anyone could help?

I have a column in excel with contact details from people in it. Th
details have came from an access database. Unfortunately, when th
database was originally made, there was only one field for name.

It has ended up with the persons Title, Forename and Surname in on
field. Not all of the entries have first names or titles, and sone o
the Forenames are in two parts i.e. Marc Luc (being first name
Davidson (being second name).

So when I do "Text to Columns" (using a space as the delimiter) I ge
the data spread across 4 rows.

Is there any way to do a more intellitgent text to columns? So that
could tell excel that the first space part in 1 coulmn, then the secon
two parts (if there are any) is the second column, and the the thir
part in the third column?

As if telling excel to skip the third space when using the delimiter?

Any hlp would be greatly appreciated, my apologies that my questin i
rather long.

Regards,

Mark Pitchfor
 
You could just stick to the 4 columns and make an extra
column that combines the text in column 2 and 3:

=A1&" "&B1

Then you copy this new column and paste it as the values.

regards,
Hans
 
Here's a slight refinement on your approach. Make them appear in
order: last name, title, first name, middle name (if exists).

In cell A1: data
Mr Marc Luc Davidson

In cell B1: last name
=MID(A1,FIND(CHAR(22),SUBSTITUTE(A1,"
",CHAR(22),LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(32),""))))+1,999)

In cell C1: title
=MID(A1,1,FIND(CHAR(22),SUBSTITUTE(A1,CHAR(32),CHAR(22),1))-1)

In cell D1: first name
=MID(SUBSTITUTE(SUBSTITUTE(A1,CHAR(32),CHAR(22),1),CHAR(32),CHAR(23),1),FIND(CHAR(22),SUBSTITUTE(SUBSTITUTE(A1,CHAR(32),CHAR(22),1),CHAR(32),CHAR(23),1))+1,FIND(CHAR(23),SUBSTITUTE(SUBSTITUTE(A1,CHAR(32),CHAR(22),1),CHAR(32),CHAR(23),1))-FIND(CHAR(22),SUBSTITUTE(SUBSTITUTE(A1,CHAR(32),CHAR(22),1),CHAR(32),CHAR(23),1))-1)

In cell E1: middle name if it is present
=IF(ISERR(MID(SUBSTITUTE(SUBSTITUTE(A1,CHAR(32),CHAR(22),1),CHAR(32),CHAR(23),1),FIND(CHAR(22),SUBSTITUTE(SUBSTITUTE(A1,CHAR(32),CHAR(22),2),CHAR(32),CHAR(23),2))+1,FIND(CHAR(23),SUBSTITUTE(SUBSTITUTE(A1,CHAR(32),CHAR(22),2),CHAR(32),CHAR(23),2))-FIND(CHAR(22),SUBSTITUTE(SUBSTITUTE(A1,CHAR(32),CHAR(22),2),CHAR(32),CHAR(23),2))-1)),"",MID(SUBSTITUTE(SUBSTITUTE(A1,CHAR(32),CHAR(22),1),CHAR(32),CHAR(
3),1),FIND(CHAR(22),SUBSTITUTE(SUBSTITUTE(A1,CHAR(32),CHAR(22),2),CHAR(32),CHAR(23),2))+1,FIND(CHAR(23),SUBSTITUTE(SUBSTITUTE(A1,CHAR(32),CHAR(22),2),CHAR(32),CHAR(23),2))-FIND(CHAR(22),SUBSTITUTE(SUBSTITUTE(A1,CHAR(32),CHAR(22),2),CHAR(32),CHAR(23),2))-1))
 
Back
Top