0

How do I identify the character é in a value and replace it with e?

I am using Excel to hold data for a MailMerge. Each column represents a piece of data that is either used in the MailMerge or uploaded onto a CRM.

In the case of French names there are often characters like é in a last name, for example: Bélanger. It would be correct to address someone by "Ms. Bélanger" but "jenny.bélanger@url.com" is not allowed.

For the sake of this question assume that columns A, B, C are First, Last, Email respectively.

A and B are entered in manually. Column C contains an email that is generated using concatenate and following the company email naming convention.For example if email naming convention is "first.last@url.com" the formula is =concatenate(lower(a1),"."lower(b1),"@url.com")

Is it possible to enter a French name correctly as a value in the name column but automatically replace an é with e for the email value?

0

Your best bet is probably to wrap your output in a series of SUBSTITUTE functions, for example

=SUBSTITUTE(
    SUBSTITUTE(
        CONCATENATE( LOWER(A1), ".", LOWER(B1), "@url.com" ),
    "á", "a"),
"é", "e")

for all the letters that you might need to replace.

0

Open the VBA macro editor and insert this code in a module:

Public Function DeFrenchify(s As String) As String

    s = VBA.Replace(s, "é", "e")
    s = VBA.Replace(s, "ç", "c")
    s = VBA.Replace(s, "è", "e")
    '...add more replacements here for all characters...

    DeFrenchify= s
End Function

Then on your worksheet, if your original names are on cell A1, for example, just use

=DeFrenchify(A1)

in formulas to get what you want.

You need to have macros enabled for this to work; and be advised that this is not very good in terms of performance, but you shouldn't worry unless you have thousands of names and they change a lot. But if your spreadsheet starts to feel slow after this, get a better solution :-)

  • Thanks for providing this. I took the easy way out by using the other response but I'm looking forward to trying this out when I have to DeFrenchify more than 100 names – Jon Jan 21 '15 at 19:41

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.