Removing non numeric and special characters

Dear all

im looking for an formula which will remove all non numeric in a cell and also "some" special characters. lets say i have in cell A1 following value: 0206-00269149.70M and i need it without "-" and "M" but keeps the "." where it is. A formula which could be edited with more special characters if needed. Final Value should be: 020600269149.70


I found a few formulas online, but those are almost all just where it removes all non numeric values and that would work for me.

Please assist on above - every info is much appreciated!

many thanks!

Try this

=SUBSTITUTE(SUBSTITUTE(A1,"-",""),"M","")

Data Range

	A	B
1	0206-00269149.70M	020600269149.70

Originally Posted by katu

Thanks Sir! Much appreciated!

Just one more: how would i do it if i had to substitute more then just "-" and "M"? is it necessary to add (SUB for all single character? lets say i have to exclude the whole alphabet?

Yes, you would have to add more SUBSTITUTES or you would have to look for a vba solutions.

This will extract the numbers from text regardless of the text that contains the number.

Formula:

Please Login or Register  to view this content.

	A	B
1	this value 123.456 is in the middle @#$#@$#@$%#!!	123.456
2	The number 20m is really text	20

You can also get this done with this formula

=SUBSTITUTE(LEFT(A1,LOOKUP(1,-MID(A1,ROW(A$1:A$99),1),ROW(A$1:A$99))),"-","")

Data Range

	A	B
1	0206-00269149.70M	020600269149.70
2	065456-456878.84PP	065456456878.84
3	02-326578988.54LLL	02326578988.54