software

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| lt;--- 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 quot;Jquot; and the quot;Bquot; are added into a single column
so its like quot;JBquot;Mr | John | Smith |
Mr | JS | Smith |
Mr | JB | Smith | lt;--- spans 3
Mr | J.C | Smith |

Any help with this would be much appreciated!

Regards,

PC--
paperclip
------------------------------------------------------------------------
paperclip's Profile: www.excelforum.com/member.php...oamp;userid=32219
View this thread: www.excelforum.com/showthread...hreadid=541520Assuming 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=quot;quot;,B1,CONCATENATE(B1,C1))
cell H1 use the formula =IF(D1=quot;quot;,C1,D1)

HTH

Ian

quot;paperclipquot; wrote:

gt;
gt; I have delimited a column of data that had a list of peoples names
gt; including their title to several columns. Unfortunately the format was
gt; not uniform in the column so some included their first and middle
gt; initials and they either separated it by a space or a period - so some
gt; people had put:
gt;
gt; eg.
gt; Mr John Smith
gt; Mr JS Smith
gt; Mr J B Smith
gt; Mr J.C Smith
gt;
gt; So by delimiting by spaces it meant most spanned 3 columns while others
gt; spanned 4 columns.
gt;
gt; What I want to do is for those that spanned 4 (because they put first
gt; initial/name and then [separated by a space] put their middle name or
gt; initial) is to add them together.
gt;
gt; eg.
gt; Mr | John | Smith |
gt; Mr | JS | Smith |
gt; Mr | J | B | Smith| lt;--- spans 4
gt; Mr | J.C | Smith |
gt;
gt; With regards to my example for Mr J B Smith, I need a way to add the
gt; two columns so that the quot;Jquot; and the quot;Bquot; are added into a single column
gt; so its like quot;JBquot;
gt;
gt;
gt; Mr | John | Smith |
gt; Mr | JS | Smith |
gt; Mr | JB | Smith | lt;--- spans 3
gt; Mr | J.C | Smith |
gt;
gt; Any help with this would be much appreciated!
gt;
gt; Regards,
gt;
gt; PC
gt;
gt;
gt; --
gt; paperclip
gt; ------------------------------------------------------------------------
gt; paperclip's Profile: www.excelforum.com/member.php...oamp;userid=32219
gt; View this thread: www.excelforum.com/showthread...hreadid=541520
gt;
gt;


OMG your a freakin' genius - i thought it impossible! :-) Shows how much
I know!!--
paperclip
------------------------------------------------------------------------
paperclip's Profile: www.excelforum.com/member.php...oamp;userid=32219
View this thread: www.excelforum.com/showthread...hreadid=541520