Remove spaces in Excel cells
Solved
féféx
Posted messages
34
Status
Membre
-
DodoBebe -
DodoBebe -
Hello everyone,
I am currently creating an Excel file for my accounts.
For that, I am copying and pasting my summary table that I find on my bank's website.
Once pasted into Excel, the cells containing the numbers are in the form (for example 100€):
+ 100 EUR
with: a space before the + and a space after.
To create my indicators, I would like my cells to only contain the numbers without spaces. (I will then automatically add the € symbol by setting my cell to the "currency" format.)
But here's the problem: I can't remove the spaces without doing it one by one.
Usually, I use the CTRL + F function to replace. This is what I do to remove 'EUR' by replacing it with nothing.
However, it doesn't work to remove the space. When I replace " " (I just put a space) with (and I put nothing), Excel tells me: "Microsoft Office Excel cannot find a match."
Do you know if the space is named by a special command?
For example, I know that the line break is named ^p and I was thinking that perhaps the space might be something similar.
Or maybe it's necessary to create a macro?
I hope I have been clear and I hope you can help me!
Thank you in advance
Félix
Configuration: HP Pavilion dv6000 (laptop)
processor core 2 duo T7300 2Ghz, 2GB of RAM
Windows Vista OEM
I am currently creating an Excel file for my accounts.
For that, I am copying and pasting my summary table that I find on my bank's website.
Once pasted into Excel, the cells containing the numbers are in the form (for example 100€):
+ 100 EUR
with: a space before the + and a space after.
To create my indicators, I would like my cells to only contain the numbers without spaces. (I will then automatically add the € symbol by setting my cell to the "currency" format.)
But here's the problem: I can't remove the spaces without doing it one by one.
Usually, I use the CTRL + F function to replace. This is what I do to remove 'EUR' by replacing it with nothing.
However, it doesn't work to remove the space. When I replace " " (I just put a space) with (and I put nothing), Excel tells me: "Microsoft Office Excel cannot find a match."
Do you know if the space is named by a special command?
For example, I know that the line break is named ^p and I was thinking that perhaps the space might be something similar.
Or maybe it's necessary to create a macro?
I hope I have been clear and I hope you can help me!
Thank you in advance
Félix
Configuration: HP Pavilion dv6000 (laptop)
processor core 2 duo T7300 2Ghz, 2GB of RAM
Windows Vista OEM
7 réponses
Hello,
The problem often arises from the fact that the space from the site is a non-breaking space.
To work around the issue, you need to copy/paste the space into the "replace" dialog box instead of typing a space in this dialog box.
Another approach is to use the CODE function to determine if the space is code 32 or code 160. Then, simply use the function =SUBSTITUTE(A,CAR(160),"").
The problem often arises from the fact that the space from the site is a non-breaking space.
To work around the issue, you need to copy/paste the space into the "replace" dialog box instead of typing a space in this dialog box.
Another approach is to use the CODE function to determine if the space is code 32 or code 160. Then, simply use the function =SUBSTITUTE(A,CAR(160),"").
And we can select the first 3 characters (non-breaking space, plus sign, normal space) to paste in the Search window...
The result remains in Text format: it needs to be converted to Number format!
So another solution would be to use an empty column where we would put the formula =VALUE(MID(cell;4;99))