+ Reply to Thread
Results 1 to 19 of 19

Formula to find and replace special characters according to a table

  1. #1
    Registered User
    Join Date
    07-11-2017
    Location
    Edhome, where else?
    MS-Off Ver
    2016
    Posts
    48

    Question Formula to find and replace special characters according to a table

    Hi all,

    I cannot figure out a formula to replace specific characters in a cell by values from a table.

    This wonderful forum has already helped my find a great solution in VBA.
    Despite that I would like to find a way to do the same with a formula.

    Rpublique > Republique

    I have a table with the special characters and the replacement characters.
    Attached file contains the table and some example texts.
    (not all cells have special characters)

    Can somebody help me create the formula?
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Formula to find and replace special characters according to a table

    This is a very long formula but I'm not key in.
    Put his in
    B2
    SUBSTITUTE(H2,C$2,D$2)

    B3:B64
    ="SUBSTITUTE("&B2&",C$"&ROW()&",D$"&ROW()&")"

    Then copy B64 and paste value to J2
    Edit and add = at the beginning.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    07-11-2017
    Location
    Edhome, where else?
    MS-Off Ver
    2016
    Posts
    48

    Re: Formula to find and replace special characters according to a table

    Hi Bo_Ry,

    Thanks for the quick reply. That's quite a long formula indeed.
    I had figured out something similar prior to this post but hoped to find a shorter solution.

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Formula to find and replace special characters according to a table

    Not with Excel2016

    This is for MS2019 or MS365

    =CONCAT(IFERROR(VLOOKUP(T(IF(1,MID(H2,SEQUENCE(LEN(H2)),1))),Table1&"",2,),MID(H2,SEQUENCE(LEN(H2)),1)))

    or UDF
    =rep(H2,Table1)

    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-11-2017
    Location
    Edhome, where else?
    MS-Off Ver
    2016
    Posts
    48

    Re: Formula to find and replace special characters according to a table

    Okay, I will try to test that on a MS365 version and let you know.

    Thanks!

  6. #6
    Registered User
    Join Date
    07-11-2017
    Location
    Edhome, where else?
    MS-Off Ver
    2016
    Posts
    48

    Re: Formula to find and replace special characters according to a table

    Tested it on MS365 and the Concat formula worked perfectly. Thank you!

    Not all users have MS2019 or MS365 so I will go with the UDF solution.

    However, the result of the function from cell H4 (ٟ ) shows lower case values (ouy a).

    Does this have to do with the fact that the function is not taking Lower and Upper Case into consideration and therefore finds (in the table)
    a lowercase value () at row 24 instead of the uppercase value () at row 57 and
    a lowercase value () at row 29 instead of the uppercase value () at row 34?

    If so, how should I adjust the function to prevent that?

  7. #7
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Formula to find and replace special characters according to a table

    For MS365 Try at
    E2
    =UNICODE(C2)

    K2
    =CONCAT(IFERROR(XLOOKUP(UNICODE(MID(H2,SEQUENCE(LEN(H2)),1)),Table1[Unicode],Table1[Repl Char])&"",MID(H2,SEQUENCE(LEN(H2)),1)))

    New Functions in Microsoft 365
    https://bettersolutions.com/excel/fu...-functions.htm
    Attached Files Attached Files
    Last edited by Bo_Ry; 03-14-2021 at 03:16 AM.

  8. #8
    Registered User
    Join Date
    07-11-2017
    Location
    Edhome, where else?
    MS-Off Ver
    2016
    Posts
    48

    Re: Formula to find and replace special characters according to a table

    Sorry Bo-Ry but as soon as I change one thing in that formula I get a #NAME error.

    It has to do with my version of Excel (https://support.microsoft.com/en-us/...4-9fbb77fd5025


    That's why I was thinking of using the UDF instead.
    Is it impossible to adapt the function?

  9. #9
    Registered User
    Join Date
    07-11-2017
    Location
    Edhome, where else?
    MS-Off Ver
    2016
    Posts
    48

    Re: Formula to find and replace special characters according to a table

    Hi all,

    As my version of Excel does not support "CONCAT", can someone help me get the suggested UDF adapted so it will check on a exact match?

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Office 2010
    Posts
    27,167

    Re: Formula to find and replace special characters according to a table

    As per post #4

    =rep(H2,Table1)


    in I2 and copy down ??

  11. #11
    Registered User
    Join Date
    07-11-2017
    Location
    Edhome, where else?
    MS-Off Ver
    2016
    Posts
    48

    Re: Formula to find and replace special characters according to a table

    Hi John,

    As replied in post #6 :

    However, the result of the function from cell H4 (ٟ ) shows lower case values (ouy a).

    Does this have to do with the fact that the function is not taking Lower and Upper Case into consideration and therefore finds (in the table)
    a lowercase value () at row 24 instead of the uppercase value () at row 57 and
    a lowercase value () at row 29 instead of the uppercase value () at row 34?

    If so, how should I adjust the function to prevent that?

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Office 2010
    Posts
    27,167

    Re: Formula to find and replace special characters according to a table

    This inelegant code works OK on the small sample
    Please Login or Register  to view this content.
    It uses the table "TableCodes" to handle upper/lower case
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    07-11-2017
    Location
    Edhome, where else?
    MS-Off Ver
    2016
    Posts
    48

    Re: Formula to find and replace special characters according to a table

    Hi John,

    Thanks a million! With this adjustment I've managed to get the data in the sheet as desired.

    Really grateful and happy with this forum.

  14. #14
    Registered User
    Join Date
    07-11-2017
    Location
    Edhome, where else?
    MS-Off Ver
    2016
    Posts
    48

    Question Re: Formula to find and replace special characters according to a table

    I'm back with an issue on this solution.

    If the special character is a single quote, it should be replaced by an space.
    Although it looks like that is what is happening, in fact it's not.

    I've tested it with the word this text : d'Albon (length is 7)

    The replaced value SHOWS: d Albon (length is 7)

    When the 2 cells (L2 and M2) are matched the result is FALSE.
    Somehow Excel has difficulties with the replacement character for the single quote.

    Attached a small file with all values and code.

    Your help is highly appreciated.
    Attached Files Attached Files

  15. #15
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Office 2010
    Posts
    27,167

    Re: Formula to find and replace special characters according to a table

    Excel does not have difficulty ....

    First, you have changed the requirement from "d'aAlbon" to (original) "dAlbon" to (new) "d Albon" and secondlyly, you want the blank to be ASCII code 160 rather than ASCII code 32 (normal blank). The ooriginal code coverted "'" to " " then REMOVED blanks as per requirement.

    For the latter change (insert code 160) , all that is required is to change the codes in sheet "SpecChar": done in attached.
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    07-11-2017
    Location
    Edhome, where else?
    MS-Off Ver
    2016
    Posts
    48

    Re: Formula to find and replace special characters according to a table

    Goodmorning John,

    Thank you for the reply.
    You are right, I did not express myself clearly (forgive me, my English is not so good)

    My requirement did not change really. I do need the function to change d'Albon to d Albon.
    So I tested it with code 160 AND 32.

    With code 160 the result looks like this: d Albon
    With code 32 the result looks like this: dAlbon

    As I do not want the space to be removed I thought code 160 was the best code to use.

    Turns out that IF you compare the result (using 160) with plain text (which is something I need to do after this conversion),
    the number of characters are identical (7 in both) but the comparison (are the values in the cells truly identical) gives FALSE as result.

    That is something I do not understand:
    What I see in the cells looks identical, the number of characters are identical, but if you have Excel compare the cells the result is FALSE.

  17. #17
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Office 2010
    Posts
    27,167

    Re: Formula to find and replace special characters according to a table

    The Match does not work because one has a value of 32 (for the blank) while the other has a value of 160. The comparsion will be done on a numerical - each character has a code so there is no match.

    Please Login or Register  to view this content.

  18. #18
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Formula to find and replace special characters according to a table

    Please change white space char 160 to normal space 32

    then UDF =rep(H3,$A$2:$A$7)

    Please Login or Register  to view this content.
    Attached Files Attached Files

  19. #19
    Registered User
    Join Date
    07-11-2017
    Location
    Edhome, where else?
    MS-Off Ver
    2016
    Posts
    48

    Re: Formula to find and replace special characters according to a table

    Both solutions work great!

    It's great to have these 2 solutions as both have taught me (again) that there are multiple ways of achieving goals.

    Thank you so much for your insights and patience to help me, much appreciated.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Find and Replace Macro to replace list of special characters
    By dcowiesmith in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-22-2021, 02:01 PM
  2. Help to create a macro - Find special characters and replace
    By Brad010140 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-29-2017, 12:41 PM
  3. Find & Replace all special characters with regular
    By mike_vr in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-05-2017, 04:23 AM
  4. find and replace special characters
    By irfanparbatani in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-15-2015, 09:30 PM
  5. [SOLVED] Find and replace special characters (unicode)
    By joevan1 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-10-2013, 06:05 AM
  6. Replies: 1
    Last Post: 05-25-2006, 11:29 AM
  7. [SOLVED] How do I find replace special characters?
    By zzapper in forum Excel General
    Replies: 1
    Last Post: 06-27-2005, 02:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1