Remove spaces between numbers

[AndrejBI]

Hi everyone,

I am clueless at the moment, I have tried all the methods available on the internet, I am starting to think it's not a space but a special character in the data set. 

Here is the file@:

https://easyupload.io/cvlp63

[Anonymous]

Hi,

I had the same problem and I found an easy solution:

Select the column and click on Replace values. Then open the advanced options and select Replace using special characters -> Non-breaking Space

After selecting it, it will populate the Value to Find:

and just click OK.

[v-rzhou-msft · Accepted solution]

Hi @AndrejBI 

I download your pbix file and have a test. The special character may not be space, I copy value and delete number value to get it.

I think you can use SUBSTITUTE Function to achieve your goal.

Calculated column:

NewTotal = 
VAR _col = Overalll[Total]
VAR _Result =
    SUBSTITUTE ( _col," ", "" )
RETURN
_Result

Measure:

M.NewTotal = 
VAR _col = MAX(Overalll[Total])
VAR _Result =
    SUBSTITUTE ( _col," ", "" )
RETURN
_Result

  Result:

You can download the pbix file from this link: Remove spaces between numbers

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[amitchandak]

@AndrejBI .

Try new column in M

Text.Start([Total],1) & Text.End([Total,3)

Try new column in Dax

left([Total],1) & right([Total,3)

[AndrejBI]

Thanks @amitchandak,

This works short-term. I want to update the data in the future, thus there will be sometimes 2 numbers on the left instead of 1. Is there any way to overcome this problem without changing the formula every time.

Also, there might be a situation when there is just 1 number on the left for some regions and then 2 for others.