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?

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.

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

Thanks!

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?

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?

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?

As per post #4

=rep(H2,Table1)


in I2 and copy down ??

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?

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

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.

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.

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.

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.

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.

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.