Feb 15 2020 11:12 AM
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
Feb 15 2020 11:19 AM
Feb 15 2020 11:50 AM - edited Feb 15 2020 11:53 AM
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" ) |
Feb 15 2020 03:25 PM
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