Getting rid of ' in Cafe

[bucci35]

Does anyone know how to get rid of the ' in the word Cafe? When I concatenate it with another word this mark goes away, but otherwise I can't get rid of it and it causes problems with a vlookup.

Thanks

 

[Tom Urtis]

Chances are that really, it is not the accent grave character itself, but a character that is a combination of the letter "e" and the acent grave character, meaning, one character é or è depending on what you actually have. So, that means it is either ascii # 232 or 233.

Try this, select your range of interest, press Ctrl+H, and activate the Replace tab.

In the Find What field, press the Alt key and enter 0233 on your keyboard to simulate that character.

In the Replace with field, enter the letter e

Click the Replace All button amd see if that does it. If not, repeat the above steps but this time, for Find what, enter Alt+0232 and replace with e.

 

[bucci35]

Do I type 0233 while holding down the alt key? If so, nothing shows up?

 

[Blade Hunter]

Do I type 0233 while holding down the alt key? If so, nothing shows up?

Yes, hold the ALT key and type 0233 on the NUMPAD. Then let go of alt and the letter will appear.

 

[bucci35]

Nothing happens. I am using a laptop and don't have a numpad, does this matter?

 

[Blade Hunter]

Yes, but we can do this another way.

Open the VBE in Excel and bring up the immediate window (CTRL-G)

Paste this in (including the ?)

?chr$(0223)

Hit enter, then the character it returns, copy that and paste it into your search. Change the 0223 for any char you want.

 

[bucci35]

This is the word I am trying to fix - Café
0232 gives me è
0223 gives me ß

Neither one seems to be the correct symbol? Am I doing something wrong again?

 

[Tom Urtis]

If Café is the only word, then do a Find and Replace for
Café

with
Cafe

 

[bucci35]

Wow...your a genious!! Thank you very much. I never thought of that one.

have a great night!!

 

[bobinnc]

I also suggest that you open "Tools", and then "Auto Correct Options...". In the "AutoCorrect" tab, scroll down the list to find "cafe" being replaced with "café". Select and delete that row so Excel no longer put "café" when you typed "cafe".