CODEWITHSUNDEEP
excelwindowspowerbipowerquery
Yury Moskaltsov
Yury Moskaltsov

Reputation: 57

Remove all whitespaces in Windows Excel

At work some of the data comes in the Excel form where some columns have numbers where thousands are split by white spaces something like 2 000 320.00

I would like to remove the whitespaces and convert it to number. When I am using my Mac I have no trouble I just use replace function, put the whitespace in the first box and then nothing in the second one and it all works.

However, recently I started working with PowerQuery and Excel in Windows and it doesn't work.

I tried using the same method in PowerQuery but it doesn't replace values

I also tried

Substitue(cell," ","")

In Excel

I even imported a Python Script into PowerQuery which is

dataset['Turnover'] = dataset['Turnover'].str.replace(" ","")

The script doesn't throw an error but it doesn't remove spaces either.

Can anybody help me with that? It seems super easy but I have wasted a ridiculous amount of time on this.

Upvotes: 0

Views: 664

Answers (1)

teylyn
teylyn

Reputation: 35915

What you call "white space" can be either a space character or a non-breaking space character, or even any other character that simply appears as a blank, for example a line feed or Enter may show as a space if the line feed cannot be rendered.

You therefore need to find much more than just a " ".

You may want to try and identify the character before you replace it. Copy the cell into Excel and if, for example it looks like this text in cell B1:

enter image description here

The third character appears to be a space. I get the actual code for that character with the formula

=CODE(MID(B1,3,1))

Now I know it's character 160, the non-breaking space, which is often in text that is copied from web sites.

On a Windows computer, you can generate this character by holding down the Alt key and typing 0160 on the number keypad.

In Excel, you can simply edit the cell, select and copy the character (regardless of what its underlying code is), then use what you just copied and paste it into the Find/Replace dialog.

In Excel's Power Query editor, you can use the "Replace Values" command and then the dropdown "Insert special character" to insert a non-breaking space. You see that in Power Query, the non-breaking space is addressed with the code #(00A0). There are several other special characters that you can select.

enter image description here

Note: The term "white space" is often used in page layout and web design to describe the area on the page that doesn't have text or images. This page you're looking at has a lot of white space on the left and right, whereas the actual page content is displayed in a center panel.

Upvotes: 1

Related Questions