Adding Text from seperate columns

[paperclip]

I have delimited a column of data that had a list of peoples names
including their title to several columns. Unfortunately the format was
not uniform in the column so some included their first and middle
initials and they either separated it by a space or a period - so some
people had put:

eg.
Mr John Smith
Mr JS Smith
Mr J B Smith
Mr J.C Smith

So by delimiting by spaces it meant most spanned 3 columns while others
spanned 4 columns.

What I want to do is for those that spanned 4 (because they put first
initial/name and then [separated by a space] put their middle name or
initial) is to add them together.

eg.
Mr | John | Smith |
Mr | JS | Smith |
Mr | J | B | Smith| <--- spans 4
Mr | J.C | Smith |

With regards to my example for Mr J B Smith, I need a way to add the
two columns so that the "J" and the "B" are added into a single column
so its like "JB"


Mr | John | Smith |
Mr | JS | Smith |
Mr | JB | Smith | <--- spans 3
Mr | J.C | Smith |

Any help with this would be much appreciated!

Regards,

PC

 

[Ian P]

Assuming your example:

Mr | John | Smith |
Mr | JS | Smith |
Mr | J | B | Smith|
Mr | J.C | Smith |

starts at cell A1, then:

cell F1 use the formula =A1,
cell G1 use the formula =IF(D1="",B1,CONCATENATE(B1,C1))
cell H1 use the formula =IF(D1="",C1,D1)

HTH

Ian

 

[paperclip]

OMG your a freakin' genius - i thought it impossible! Shows how muc
I know!