Forum Discussion
eliminating hyphen or dashes from a table of numbers
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
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
3. Click on Replace Values in the Home tab
4. Type in - in the Value To Find box
5. Click OK. There you have it in the caption below
Finally, Select Close and Load to load the result back to Excel
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 text Digits as Text Number Formatted Number 00563-0125-01 00563012501 563012501 00563-0125-01 = SUBSTITUTE( formattedText, "-", "" ) = VALUE( digitsAsText ) 00000-0000-00 Text 00563-0125-01 = TEXT( number, "00000-0000-00" )
