Forum Discussion
Two set of words that looks exactly the same but somehow they are different
Hi, I have one issue that I have been trying to solve for the last 3 days but could not find what is wrong. I am not sure if this is an excel bug (or the nature of excel) or if I am doing something wrong.
As you can see in the below picture, words in A1 and A2 are exactly the same. However, they react differently to the same formula. B2:C4 have formulas that tries to detect how many words mentioned in B1 and C2, respectively (something like =COUNTIF($A2,"*"&B$1&"*")).
What I know at the moment is, if you delete the space in A2 and refill the space by typing the space key, it starts detecting the words. I assume something is hidden in the space so that the system assumes words in A3 are without any spaces. I have 5,000 raws of such words data, and when I try to replace the space using ctl+F4, it did not work. Could anyone what is happening in this spreadsheet?
https://1drv.ms/x/s!AitI3rLN-CbMtC569dUFkyb5pMkO?e=jVuooc
https://1drv.ms/x/s!AitI3rLN-CbMtC569dUFkyb5pMkO?e=jVuooc
regards
The spaces in cell A2 are non-breaking spaces (ASCII code 160).
All spaces in cell A3 are ordinary spaces (ASCII code 32).
You can do the following:
- Select one of the (non-breaking) spaces in cell A2 and copy it (Ctrl+C).
- Select the entire range.
- Press Ctrl+H to activate the Replace dialog.
- Click in the 'Find what' box and paste (Ctrl+V).
- Click in the 'Replace with' box and press the spacebar.
- Click 'Replace All'.
4 Replies
The spaces in cell A2 are non-breaking spaces (ASCII code 160).
All spaces in cell A3 are ordinary spaces (ASCII code 32).
You can do the following:
- Select one of the (non-breaking) spaces in cell A2 and copy it (Ctrl+C).
- Select the entire range.
- Press Ctrl+H to activate the Replace dialog.
- Click in the 'Find what' box and paste (Ctrl+V).
- Click in the 'Replace with' box and press the spacebar.
- Click 'Replace All'.
- Thank you so mcuh!! I did not know there is difference between spaces.
>Click in the 'Replace with' box and press the spacebar.
I found on my end it did not work only by pressing the spacebar. I also copied and pasted ordinary space with Ctrl+C to replace. You saved my time so much. Thank you very much..!
- Thank you very much!! I think it automatically replaced non-breaking spaces to spaces.
