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