How to Convert A String Containing Dash into Number

PROBLEM STATEMENT

I have a text string in format 9876-54321. How can I convert it from text to number format like 987654321?

APPROACH

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.

SOLUTION

=(SUBSTITUTE(TRIM(MyDashedNumber),"-",""))*1

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 ().

Find-and-Replace-Microsoft-Excel

There you go 🙂

Advertisements
How to Convert A String Containing Dash into Number

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s