Home

eliminating hyphen or dashes from a table of numbers

%3CLINGO-SUB%20id%3D%22lingo-sub-1175787%22%20slang%3D%22en-US%22%3Eeliminating%20hyphen%20or%20dashes%20from%20a%20table%20of%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1175787%22%20slang%3D%22en-US%22%3E%3CP%3EAs%20an%20example%20of%20a%20string%20of%20numbers%20in%20a%20column%20they%20have%2011%20digits%20separated%20by%203%20dashes.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%2000563-0125-01%26nbsp%3B%20I%20want%20to%20get%20rid%20of%20the%20dashes%20to%20leave%20a%20format%20like%2000536012501%20and%20the%20zero%20placeholders%20are%20significant.%26nbsp%3B%20Is%20there%20a%20method%20to%20get%20rid%20of%20the%20dashes%20in%20columns%20of%20numbers%20of%20this%20layout%20xxxx-xxxx-xx%26nbsp%3B%20to%20become%20xxxxxxxxxxx%20using%20an%20excel%20function%20or%20process%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1175787%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1175792%22%20slang%3D%22en-US%22%3ERe%3A%20eliminating%20hyphen%20or%20dashes%20from%20a%20table%20of%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1175792%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F558874%22%20target%3D%22_blank%22%3E%40Bruce_K2952%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20remove%20the%20hyphens%20from%20a%20text%20string%20following%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%20's%20formula.%3C%2FP%3E%3CP%3EYou%20can%20go%20on%20to%20convert%20it%20to%20a%20number%20but%20that%20loses%20leading%20zeros%3C%2FP%3E%3CP%3EYou%20can%2C%20though%2C%20format%20the%20number%20so%20that%20it%20is%20displayed%20in%20the%20original%20format.%3C%2FP%3E%3CP%3EFinally%20you%20can%20close%20the%20loop%20and%20turn%20the%20number%20back%20to%20text%20in%20the%20required%20format.%3C%2FP%3E%3CTABLE%20width%3D%22536%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22128%22%3E%3CSTRONG%3EFormatted%20text%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22128%22%3E%3CSTRONG%3EDigits%20as%20Text%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22152%22%3E%3CSTRONG%3ENumber%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%20width%3D%22128%22%3E%3CSTRONG%3EFormatted%20Number%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E00563-0125-01%3C%2FTD%3E%3CTD%3E00563012501%3C%2FTD%3E%3CTD%3E563012501%3C%2FTD%3E%3CTD%3E00563-0125-01%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CFONT%20color%3D%22%233366ff%22%3E%3D%20SUBSTITUTE(%20formattedText%2C%20%22-%22%2C%20%22%22%20)%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%233366ff%22%3E%3D%20VALUE(%20digitsAsText%20)%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20color%3D%22%233366ff%22%3E00000-0000-00%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CSTRONG%3EText%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E00563-0125-01%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CFONT%20color%3D%22%233366ff%22%3E%3D%20TEXT(%20number%2C%20%2200000-0000-00%22%20)%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1175788%22%20slang%3D%22en-US%22%3ERe%3A%20eliminating%20hyphen%20or%20dashes%20from%20a%20table%20of%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1175788%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F558874%22%20target%3D%22_blank%22%3E%40Bruce_K2952%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DSUBSTITUTE(A1%2C%22-%22%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BAssuming%20your%20text%20is%20in%20A1%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1175823%22%20slang%3D%22en-US%22%3ERe%3A%20eliminating%20hyphen%20or%20dashes%20from%20a%20table%20of%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1175823%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F558874%22%20target%3D%22_blank%22%3E%40Bruce_K2952%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20use%20Power%20Query%20to%20also%20remove%20all%20the%20dash%3C%2FP%3E%0A%3CP%3E1.%20Format%20the%20data%20in%20Excel%20Table%3C%2FP%3E%0A%3CP%3E2.%20Select%20From%20Table%2FRange%20in%20Data%20tab%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Abiola1_1-1581808852141.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F171399i2A8BEE2F142D6312%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Abiola1_1-1581808852141.png%22%20alt%3D%22Abiola1_1-1581808852141.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E3.%20Click%20on%20Replace%20Values%20in%20the%20Home%20tab%3C%2FP%3E%0A%3CP%3E4.%20Type%20in%20-%20in%20the%20Value%20To%20Find%20box%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Abiola1_2-1581808970141.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F171400i80CD4378D047F769%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Abiola1_2-1581808970141.png%22%20alt%3D%22Abiola1_2-1581808970141.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E5.%20Click%20OK.%20There%20you%20have%20it%20in%20the%20caption%20below%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Abiola1_3-1581809055131.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F171401iDB5C8ED324D1310F%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Abiola1_3-1581809055131.png%22%20alt%3D%22Abiola1_3-1581809055131.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EFinally%2C%20Select%20Close%20and%20Load%20to%20load%20the%20result%20back%20to%20Excel%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

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
Highlighted

@Bruce_K2952 

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

 Assuming your text is in A1

Highlighted

@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" )  
Highlighted

@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

 

Related Conversations
Counting Days
Tim Hunter in SQL Server on
2 Replies
PWA fullscreen like IE11 kiosk mode
rogihee in Discussions on
5 Replies
PivotTable : Unexpected behavior with 2 tables
CastyChichi in Excel on
2 Replies
Adding Display Zero as Dashes to Custom Excel Number Format
emmagreen in Excel on
1 Replies