eliminating hyphen or dashes from a table of numbers

Copper Contributor

As an example of a string of numbers in a column they have 11 digits separated by 3 dashes.

 

Example 00563-0125-01  I want to get rid of the dashes to leave a format like 00536012501 and the zero placeholders are significant.  Is there a method to get rid of the dashes in columns of numbers of this layout xxxx-xxxx-xx  to become xxxxxxxxxxx using an excel function or process?

 

thanks 

3 Replies

@Bruce_K2952 

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

 Assuming your text is in A1

@Bruce_K2952 

You can remove the hyphens from a text string following @Riny_van_Eekelen 's formula.

You can go on to convert it to a number but that loses leading zeros

You can, though, format the number so that it is displayed in the original format.

Finally you can close the loop and turn the number back to text in the required format.

Formatted textDigits as TextNumberFormatted Number
00563-0125-010056301250156301250100563-0125-01
    
= SUBSTITUTE( formattedText, "-", "" )= VALUE( digitsAsText )00000-0000-00
    
Text   
00563-0125-01   
= TEXT( number, "00000-0000-00" )  

@Bruce_K2952 

You can use Power Query to also remove all the dash

1. Format the data in Excel Table

2. Select From Table/Range in Data tab

Abiola1_1-1581808852141.png

3. Click on Replace Values in the Home tab

4. Type in - in the Value To Find box

Abiola1_2-1581808970141.png

5. Click OK. There you have it in the caption below

Abiola1_3-1581809055131.png

Finally, Select Close and Load to load the result back to Excel