Need Help!!

[Murty]

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 Somebody]

But if their name was:
"Joe Bump"

Then the next column should contain:
"um2" [Joe Bump] (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.

[avveerkar]

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 Somebody]

But if their name was:
"Joe Bump"

Then the next column should contain:
"um2" [Joe Bump] (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 always have a first name followed by a single space followed by a surname. Surname must have at least 3 letters. I don't know what you want to do if surname is only of two letters say AB. Should you like to have B22 or B2? This formula will give you B2. Looking at the formula you can easily guess what the formula is trying to do and modify it for two letter surname etc. If formula does not find any space ( between first name and surname ) it will give an error resulting #REF in F17
You can autofill this formula from F17 to all cells in column F

A V Veerkar

[Jim Hagan]

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

[Ron Rosenfeld]

On Tue, 7 Feb 2006 06:06:35 -0600, Murty
<[email protected]> 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

[Murty]

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?

[Ron Rosenfeld]

On Tue, 7 Feb 2006 13:19:55 -0600, Murty
<[email protected]> 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