The most reason is that your space is not a space, usually if you copy&paste some data from a web site you have an ASCII 160 in there.
Edit the cell, select the space, press CTRL+C, press ESC to exit the edit mode, press CTRL+H to open find & replace, press CTRL+V to paste "the space" into the find text field, click "Replace All".
Andreas.
Answer from Andreas Killer on learn.microsoft.com-
I have a very long list of names, where the word "and" often appears between the two names. For example, "John and Mary."
-
I want to find every occurrence of the word "and" and replace it with " ^ " (Purpose is to use as delimiter for later splitting text to column, but that is another matter). In the example above, I would like the names to be shown as "John ^ Mary".
-
My problem: I can't figure out how to get Excel to include spaces in the text that I am seeking find and replace. In other words, I want Excel to look for (space)and(space). I have typed it into find like this: " and " << (without the quote marks, but typing a space before and after the word "and").
-
However, Excel ignores the spaces when it does the replacement, which messes up my list.
More info: Issue is, many of the names on my list contain the letters "and", and Excel is finding and replacing those text strings, even though they don't have a space before and after. Example: "John and Sandra" would be replaced with "John ^ S^ra" (instead of "John ^ Sandra). Or, "Andrew" would be replaced with "^rew"
If "find" in Excel also included the spaces you entered, then the problem would be solved, but Excel apparently can't find the spaces. (This is unlike Word, which definitely can find spaces.)
I have been searching for an solution for a long time, can anyone help?
How to remove space in excel worksheet
Remove extra spaces with Find & Replace but don't remove leading zeroes?
Find and Replace: Replacing a special character with a space
replace - Excel formula to remove comma, spaces, period and add a text - Stack Overflow
The most reason is that your space is not a space, usually if you copy&paste some data from a web site you have an ASCII 160 in there.
Edit the cell, select the space, press CTRL+C, press ESC to exit the edit mode, press CTRL+H to open find & replace, press CTRL+V to paste "the space" into the find text field, click "Replace All".
Andreas.
Hi Andrea,
The issue is being fixed. Thanks so much for your help! :)
Hi Cliff!
Thanks for your comments.
I have data I want to copy from one excel doc to another. Since I'm using index match I must keep all 6 digits including zeroes (example: 000016).
The problem is that whenever new data is generated from the business software I use the exporterd data has 6 digits values with extra space in each cell... Removing spaces is easy with Find & Replace by replacing space with nothing. Problem is that this removes leading zeroes as well?
I want to remove extra space and keep leading zeroes in each cell.
The "backwards P" is called a pilcrow, and Word uses it to represent the (usually invisible) mark at the end of a paragraph. That character also appears on a button on the Home ribbon that, when turned on, makes all the nonprinting characters visible.
In the Find/Replace dialog, when wildcards are not enabled, the paragraph mark is represented by the code ^p. That code and many others can be inserted in the Find What and Replace With boxes by clicking the More button to expand the dialog and then clicking the Special button at the bottom of the expanded section.
.
If you have occasion to use wildcard searches, the code ^13 must be used in the Find What box, although ^p continues to work in the Replace With box.
Perfect... I looked for some character/code images within Microsoft and other sites. None provided the pilcrow image even when looking as ASCII and other codes. Tx