I have a text string in format 9876-54321. How can I convert it from text to number format like 987654321?
Lets think of a number that is made of numbers and dashes. Lets call it MyDashedNumber and it looks something like 565757-55855—5755—-7210. We want all the dashes gone. Our approach could be to replace all dashes with a NULL string.
HOW DOES IT WORK
TRIM(MyDashedBumber) TRIM removes any space from MyDashedNumber. I am recommending TRIM to use a to be on safer side just in case if there is any additional space in the string that you are trying to convert into a number.
SUBSTITUTE As the name itself suggests, This function is used to substitute some text from your string and replace it with a newer one specified by you. In our case, we are substituting “-” with nothing “”. Therefore this formula will remove all dashes “-” and will add nothing to string.
Multiply the Result with 1 SUBSTITUTE always returns the result as a text string (even though it is made of numbers). In Excel, we can multiply any string that is made of numbers (0,1,2,3,4,5,6,7,8,9) with 1 and convert it from a string to a number format.
Simple Solution Alternatively, you could select all the cell where you want to convert MyDashedNumber into numbers and use Find and Replace dialogue box (Ctlr+H) and Replace All dashes (“-“) with nothing ().
There you go 🙂