Murty Wrote:
I have been asked to complete an excel spreadsheet thing. But i have a
question. The spreadsheet has list of names and other details in each
column. For the column after the names, i need to do the following.

"This is the 2nd, 3rd and 5th characters from the Client's last name
excluding andy punctuation. If there is not enough letters to use the
2nd, 3rd and 5th characters then the number 2 is used to substitute the
missing characters."

So if a clients name was:
"Joe Somebody"

Then the next column should contain:
"omb" [Joe S*om*e*b*ody]

But if their name was:
"Joe Bump"

Then the next column should contain:
"um2" [Joe B*um*p] (cos there isnt a 5th, it is then substituted with a
'2')

But I do not know how to put this in place for 1 cell, let alone the
whole column. I think there might be some code written for this?

Any help is much appreciated, thanks.

Assume name is in cell E17 and result in F17 then formula for F17 will
be

=MID(E17,(FIND(" ",E17,1))+2,1) & MID(E17,(FIND(" ",E17,1))+3,1) &
IF(MID(E17,FIND(" ",E17,1)+5,1)="","2",MID(E17,FIND(" ",E17,1)+5,1))

Assumption you must have first name followed by space followed by
surname. Surname must have at least 3 letters. Don't know if surname is
of only two letters say AB, what would you like to have B22 or B2?
Looking at the formula you can easily guess what the formula is trying
to do and modify for two letter surname etc.

A V Veerkar


--
avveerkar
------------------------------------------------------------------------
avveerkar's Profile: http://www.excelforum.com/member.php...o&userid=30338
View this thread: http://www.excelforum.com/showthread...hreadid=509302