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

1. ## 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.

République > 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?

2. ## 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.

3. ## 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. ## 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.``

5. ## 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. ## 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. ## 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

8. ## 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. ## 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. ## 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. ## 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. ## 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

13. ## 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. ## 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.

15. ## 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.

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

Goodmorning John,

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. ## 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. ## 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.``

19. ## 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.

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

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