Formula Friday - Clean up phone numbers

Hi - Dave here. Happy Friday! Today I've got another good example of how new functions are completely changing the way hard problems are solved in Excel. In the worksheet below, the goal is to "clean and reformat" a list of telephone numbers that appear in various formats. The old formula involves removing extra characters one at a time with multiple nested SUBSTITUTE functions: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,"(",""),")",""),"-","")," ",""),".","")+0 The new formula is much simpler: =TEXTJOIN("",1,TEXTSPLIT(B5,{"(",")","-"," ","."},,1))+0 [A modern formula to clean up phone numbers]( [[Download the workbook and read the full explanation](] What's interesting about this (to me at least!) is that when you use TEXTSPLIT to split text with multiple delimiters, the delimiters themselves are discarded in the process. This makes TEXTSPLIT an interesting way to remove multiple characters at the same time. The drawback is that you have to combine the split text again, which is why we have TEXTJOIN in there as well. Note that TEXTSPLIT is only available in Excel 365, and TEXTJOIN is available in Excel 2019 and later. However, the SUBSTITUTE formula will work fine in older Excel versions and is included in the workbook. Read the full explanation and download the workbook at the link above. Excel formulas We maintain a list of over 500 working formulas [here](. If you need more structure, we also offer [video training](. Have a great weekend! Dave [View this email in a browser]( [Exceljet Logo]( Exceljet P.O. Box 4804 Salt Lake City, UT 84110 Copyright © 2023 Exceljet, All rights reserved. You received this email because you are subscribed to our newsletter. To unsubscribe, click the link below. [Unsubscribe](