• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with a formula please

M

MalR

Member
Hi, Nebu helped me with a formula two years ago which we use extensively. It puts surnames and first names in a configuration that I use to sort names in a data base. But it has always had a bug. For two years I corrected it manually but our database of names is now larger and requires many manual corrections. I am wondering if Nebu is still around or if anyone else can fix the formula. It is too complicated for me.

What it does is:
Column A is called Name. It contains the surnames eg Smith

Column B is called First name and it has the husband and wife’s first names. It can be written as Bill and Gwen or Bill & Gwen. If there is one person it can be written Bill &

There are other contact details in the other columns and Colum K expresses the formula that Nebu gave me. The formula returns the surname with the first letters of the first names. It gives say, AdamD&A or if a single person AdamD&. I use this to then do a name search to pull out all their details automatically.

It works very well except if the name has the word “and” in it it fails. For instance if the surname is Bourne and the first names are Peter and Sandra it will return P&a (should return P&S) because Sandra has “and” in the name

Or if the surname is Butlin, Andrew and Janet it returns Butlin A&e. It should return A&. This is because Andrew has “and” in his first name.

This is the formula

=IFERROR(IF(ISNUMBER(SEARCH("and",B2,1)),A2&LEFT(B2,1)&"&"&MID(B2,SEARCH("and",B2,1)+4,1),A2&LEFT(B2,1)&"&"&MID(B2,SEARCH("&",B2,1)+2,1)),A2&LEFT(B2,1)&"&")

Thanks your help is appreciated

MalR
 
MaIR

K2: =IFERROR(IF(ISNUMBER(SEARCH(" and ",B2,1)), A2&LEFT(B2,1)&"&" & MID(B2,SEARCH(" and ",B2,1)+5,1), A2 & LEFT(B2,1)& "&" & MID(B2,SEARCH("&",B2,1)+2,1)), A2&LEFT(B2,1) & "&")
copy down
 
Last edited:
Hi ,

It would have been better if the workbook had been made available.

However , going by your explanation , the search for the word and is responsible for detecting the and in the name Sandra.

Try using the text " and" (without the quotes) or the text "and " (without the quotes or the text " and " (without the quotes) where there are leading / trailing spaces.

The above will ensure that the and in Sandra is not detected.

Narayan
 
I really appreciate your help Hui and Narayan. Hui your formula worked. It has saved me a lot of time doing manual corrections for which I am thankful.
Narayan your suggestion helped but did not work in every case. My fault for not sending a file.
Thanks for your prompt reply as usual guys.
Mal
 
You must log in or register to reply here.
Back
Top