View original (French)

Accents / Excel

agentskiman Posted messages 3 Status Member -  
 Wahi-Em -
Need a little help

How to remove all the accents from a column of names without using an Excel Macro function?
Thank you

5 answers

  1.
    .Grincheux. Posted messages 211 Registration date   Status Member Last intervention   84
     
    Hello,

    If the number of accented characters is limited, the following function might work: 
    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE (A1,"é","e"),"è","e"),"ê","e"),"ë","e"),"É","E")


    Have a nice day!
    6
    1. Wahi-Em
       
      Awesome! That's exactly what I was looking for!!! Thank you so much!
      0
  2. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Hello,

    Do it letter by letter with accents using Ctrl+F (replace), which will be faster than writing a formula.
    eric

    --
    You will never reply to an unsolicited DM...
    Well, that's settled.
    1
  3. agentskiman Posted messages 3 Status Member
     
    Hello eriiic

    I'm working on a Mac...
    I have a column of names with accents and I don't know anything about macros.
    I need to remove all those pesky accents at once.
    Last year, I saw something online where we had to use the Paste Special in the Edit menu.
    Due to lack of practice, I've lost the recipe!!!!
    0
    1. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      If you don't want to use a macro, select your column and do Replace: é
      with: e
      and do the same for all accented letters è, ê, à, etc.
      It's quicker than explaining VBA to you (I don't know it on Mac anyway, and besides, you have Mac Office versions without VBA)

      eric
      0
  4. Raymond PENTIER Posted messages 58211 Registration date   Status Contributor Last intervention   17 480
     
    Yeah!
    "Last year, I saw something on the web where you had to use the Paste Special option in the Edit menu" ... The "thing" was to:
    - write a formula (like that of .Grumpy.) in column B to transform the data from column A,
    - select column B and click Copy
    - return to column A to do Paste Special 'Values'
    - delete column B which became unnecessary.
    Retirement is great! Especially in the Caribbean ... :-)
    ☻ Raymond ♂
    0
  6. agentskiman Posted messages 3 Status Member
     
    Hello
    Thanks for the tip. That was it.
    Between two Pina coladas, do you know how to remove accents from an entire column without using a Macro function?
    And with Excel installed on a Mac?
    LS.
    0
    1. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      ???
      You messed up the dose of rum...
      eric
      0
    2. Raymond PENTIER Posted messages 58211 Registration date   Status Contributor Last intervention   17 480
       
      On Mac or PC, it's exactly the same (unless we were using a macro).
      If you could tell us how the accents are problematic, it would help us find a workaround for the issue; moreover, it would be necessary to specify which accents we are talking about:
      * is the circumflex accent in même and the diaeresis in Noël also affected?

      As for piña colada, it is predominantly consumed in Spanish-speaking Caribbean; we prefer a planter or the "petit punch" rum-lemon!
      ...or even champagne, as Guadeloupe is the French department that consumes the most per capita!
      0