SUBSTITUTE it is easy to use
=SUBSTITUTE( SUBSTITUTE(I2,",","_") ," ","_")
Find and replace - how to include blank spaces before and after a word you want to replace
Excel formula to remove space between words in a cell - Stack Overflow
Excel function to remove spaces and replace them with commas
how to replace every underscore with a space instead?
When is it best to replace spaces with dashes in Excel using a formula?
Is it possible to apply this formula to an entire column?
Does the SUBSTITUTE function only work for spaces and dashes?
-
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?
Suppose the data is in the B column, write in the C column the formula:
=SUBSTITUTE(B1," ","")
Copy&Paste the formula in the whole C column.
edit: using commas or semicolons as parameters separator depends on your regional settings (I have to use the semicolons). This is weird I think. Thanks to @tocallaghan and @pablete for pointing this out.
It is SUBSTITUTE(B1," ",""), not REPLACE(xx;xx;xx).
Hi, I have a large data set of numbers which have spaces in between, and I want to remove the spaces and replace them with commas (effectively making me able to sum them up for instance). Which formula can I use?
I have TONS of headers that look like the following
owner2_address_1_city
There underscores instead of spaces make it hard to read quickly and make wrapping text useless. is there an easy way to change it to normal text i.e. below:
"owner 2 address 1 city"
Thanks in advance!
Have to make a ton of links to a database and can do that as the links are mostly the same but need to use the document title to mass produce so I don’t have to go and input every link. Is there a command that could convert spaces in a cell to “%20”? Couldn’t find it in their website online sorry. Edit: my version is Microsoft Excel for Microsoft 365 MSO (Version 2208
Hi,
This formula (in cell B2) will get rid of white spaces
=REGEXREPLACE(A2,"[^\S]+","")
Hope this helps.
Re: have removed spaces but some are still there.
Importing data can bring in unwanted spaces and other characters.
The 'non breaking space', character 160, is one of them.
From 10 years ago...
Using Replace:
Select the column or columns with data in it,
Press CTRL+H to bring up the Replace dialog box.
In the "Find what" field, type ALT+0160 using only the Number Pad to type in the digits.
To be safe: type a single space character into the "Replace with" field
Next, click the "Options" button to reveal all the available options and make sure
the check box labeled "Match entire cell contents" does not have a check mark in it.
Finally, click the "Replace All" button.
'---
Or you can select one of the left over spaces and paste it into the Replace dialog box.
Or you could use a formula =SUBSTITUTE(D1,CHAR(160),"")
Or you could avail yourself of my free 'Professional Compare' workbook, which has a "Clean Data" utility. It provides several options. Download from OneDrive...
https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU
'---
Nothing Left to Lose