ISO formula to remove spaces and/or dashes from a column of numbers

[paquirl]

I have a column with numbers where the user has separated portions with space(s) or a dash. Need a formula to remove them.

 

[Eric W]

Like this?

Book2
	A	B
1	Number	No separators
2	1	1
3	5	5
4	123-45	12345
5	123 45	12345
6	999-123-4567	9991234567
Sheet3

Cell Formulas
Range	Formula
B2:B6	B2	=SUBSTITUTE(SUBSTITUTE(A2," ",""),"-","")

Keep in mind that the result is a number stored as text, not a numeric value.

 

[paquirl]

Hi it is not working for me. I'm wondering if it is because i told you wrong, it's not numbers. My info is stored as text this time. I get different formats sent to me depending on the day.

 

[paquirl]

this works, the spreadsheet i have today was protected. this solution does work thank you

 

[Eric W]

Glad you got it working. Thanks for the update.

 

[sandy666]

raw	Result
41-49 060 AB-C	4149060ABC
X C-144 08 067	XC14408067
167-37-67-4	16737674
1 50 83-55 3	15083553
4 87 76-20-7	48776207

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TS = Table.AddColumn(Source, "Result", each Text.Select([raw],{"0".."9","a".."z","A".."Z"})),
    RC = Table.RemoveColumns(TS,{"raw"})
in
    RC