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.


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

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

The following spreadsheet formula should do the trick ...

With the person's name existing in cell A1 ...

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

Description
FIND(" ",A1)
The FIND function determines the string position of the single space between
the first name and last name. With the name Joe Somebody, the space occupies
position 4.

MID(A1, FIND(" ",A1)+2, 2)
MID(text, start_num, num_chars)
The MID function pulls 2 characters out of the string, starting at position
FIND(" ",A1)+2 (ie. 6). In the name Joe Somebody, this will extract the 2nd
and 3rd characters from the name, leaving the string "om".

I then use the text concatenation character (i.e. &) to add a 2nd string to
the string already found.

For the 2nd string, I use the same principle as before (ie. locate the space
between the name, extract the string that I need). The function ...

MID(A1,FIND(" ",A1)+5,1)

would extract 1 character starting at the 5th character from the single
space between names. Since this may not be present, I use the IF function to
test to see if anything is there ...

IF(MID(A1,FIND(" ",A1)+5,1)="", ...

If nothing is there, I put the number 2 ...

IF(MID(A1,FIND(" ",A1)+5,1)="", 2, ...

If something is there, I put the character that I just found ....

IF(MID(A1,FIND(" ",A1)+5,1)="", 2, MID(FIND(" ",A1)+5,1))

Hope this helps,

Jim

"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.


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

On Tue, 7 Feb 2006 06:06:35 -0600, 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.


Try this:

1. Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

2. Try this formula:

=REGEX.SUBSTITUTE(TRIM(A10)&"2222","(^\w+\s)(\w+\s )?(\b\w)(\w)(\w)(\w)(\w)(\w*$)","[4][5][7]")

I have assumed that the name consists of a First Name, followed by an optional
Middle Name or Initial, followed by the last name.

We Trim and concatenate '2's to get rid of extraneous spaces and have enough
two's in case the last name isn't long enough.

The regular expression then captures the

First Name: (^\w+\s)
Optional Middle: (\w+\s)?

And the first five letters of the last name all into separate variables:
(\b\w)(\w)(\w)(\w)(\w)(\w*$)

Variables # 4, 5, and 7 will be the 2nd, 3rd and 5th letters of the 2-padded
last name.

Let me know if this does what you want.
--ron

Thanks heaps guys.
I was also just wandering, because i have 800+ client names, is i
possible to apply this forumla to the entire column

--
Murt
-----------------------------------------------------------------------
Murty's Profile: http://www.excelforum.com/member.php...fo&userid=3125
View this thread: http://www.excelforum.com/showthread.php?threadid=50930

On Tue, 7 Feb 2006 13:19:55 -0600, Murty
wrote:


Thanks heaps guys.
I was also just wandering, because i have 800+ client names, is it
possible to apply this forumla to the entire column?

As an example, if your names are in A1:A800, enter the formula referencing A1
in B1, then fill down to B800.


--ron