Combining Data from Cells 1

[useractive]

I need to create a formula in excel that looks at column a and sees all of the words and then splits out to make an 8 character client code.

For example:

Column A Column B
Frank & Mary Jane JaneFraM
Frank Smith SmitFran
Frank Smith & Mary Jane JaneFraS

Any ideas on this one?

 

[useractive]

Oops, my bad change that.

I want it to pull the last 10 characters of the last name, and if the last name doesn't fill 10 characters it then finishes it out with the first name until 10 characters has been recognized. ie

Column A Column B
Frank Smith SMITHFRANK
David Swisher SWISHERDAV
Jim Vandeveer VANDEVEERJ

That's more what I'm looking for.

Thanks.

 

[luceze]

Something like this formula will work but it gets kind of funny if the length of the first name + last name is less than 10.

=IF(LEN(RIGHT(A3,LEN(A3)-FIND(&quot; &quot;,A3)))<10,RIGHT(A3,LEN(A3)-FIND(&quot; &quot;,A3))&LEFT(A3,10-LEN(RIGHT(A3,LEN(A3)-FIND(&quot; &quot;,A3)))))

HTH,
Eric

 

[SkipVought]

Hey user.

How 'bout this?

=LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1))&LEFT(A1,FIND(" ",A1)),10)

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[luceze]

Much more concise Skip!

Eric

 

[KenWright]

Both will fall down though if Middle initials are present, eg John A Deere, or if any of the data is as per the Ops first set of examples. Following should hold good no matter what, even in many cases (As it will repeat characters) if there are not as many (Not all cases though) as 8 Characters between all the names (And at least 1 space in there somewhere):-

=RIGHT(A1,LEN(A1)-FIND(&quot;*&quot;,SUBSTITUTE(A1,&quot; &quot;,&quot;*&quot;,LEN(A1)-LEN(SUBSTITUTE(A1,&quot; &quot;,&quot;&quot))))&LEFT(SUBSTITUTE(A1,&quot; &quot;,&quot;&quot,8-LEN(RIGHT(A1,LEN(A1)-FIND(&quot;*&quot;,SUBSTITUTE(A1,&quot; &quot;,&quot;*&quot;,LEN(A1)-LEN(SUBSTITUTE(A1,&quot; &quot;,&quot;&quot))))))

Regards
Ken.................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[useractive]

Thanks guys for all of the great input. Ken, yours worked perfectly.

Thanks again, and have a Merry Xmas.