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" wrote:

>
> 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
>
>
> --
> paperclip
> ------------------------------------------------------------------------
> paperclip's Profile: http://www.excelforum.com/member.php...o&userid=32219
> View this thread: http://www.excelforum.com/showthread...hreadid=541520
>
>

[paperclip]

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