Custom Number Format on Multiple numbers & text inside same cell

%3CLINGO-SUB%20id%3D%22lingo-sub-2074281%22%20slang%3D%22en-US%22%3ECustom%20Number%20Format%20on%20Multiple%20numbers%20%26amp%3B%20text%20inside%20same%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2074281%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20objective%20is%20to%20create%20a%20custom%20number%20format%20that%20can%20work%20for%20phone%20numbers%20AND%20dates%20inside%20the%20same%20cell.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEXAMPLE%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JohnnyB12_0-1611062472660.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F247431iF44C97F2505EF6F7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22JohnnyB12_0-1611062472660.png%22%20alt%3D%22JohnnyB12_0-1611062472660.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20the%20leftmost%20cell%2C%20I've%20applied%20the%20following%20custom%20number%20format%3A%20(%23%23%23)%20%23%23%23-%23%23%23%23%3C%2FP%3E%3CP%3EHowever%2C%20this%20format%20is%20not%20applicable%20on%20the%20rightmost%20cell%20with%20text%20because%20aside%20from%20having%20phone%20numbers%2C%20it%20also%20includes%20dates%20and%20words.%20What%20formula%20can%20be%20used%20to%20have%20all%20phone%20numbers%20in%20this%20format%3A%20(%23%23%23)%20%23%23%23-%23%23%23%20and%20also%20the%20date%20in%20this%20format%3A%20mm%2Fdd%2Fyy%2C%20while%20keeping%20text%20as%20is%3F%20(In%20this%20order%20as%20well%3A%20phone%20number%2C%20date%2C%20note)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20is%20greatly%20valued!!%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2074281%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2074357%22%20slang%3D%22en-US%22%3ERe%3A%20Custom%20Number%20Format%20on%20Multiple%20numbers%20%26amp%3B%20text%20inside%20same%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2074357%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F934533%22%20target%3D%22_blank%22%3E%40JohnnyB12%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20use%20a%20formula%20such%20as%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3D%22HOUSE%20NOTES%3A%20PHONE%20NOTES%3A%20%22%26amp%3BTEXT(A2%2C%22(%23%23%23)%20%23%23%23-%23%23%23%23%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2074402%22%20slang%3D%22en-US%22%3ERe%3A%20Custom%20Number%20Format%20on%20Multiple%20numbers%20%26amp%3B%20text%20inside%20same%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2074402%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F934533%22%20target%3D%22_blank%22%3E%40JohnnyB12%3C%2FA%3E%26nbsp%3BA%20custom%20number%20format%20doesn't%20have%20that%20capability.%26nbsp%3B%20You%20would%20have%20to%20use%20a%20macro%20to%20scan%20any%2Fall%20text%20entered%20and%20modify%20according%20to%20your%20rules%20or%20you%20could%20use%20helper%20columns%20so%20you%20enter%20it%20in%201%20column%20but%20using%20a%20sophisticated%20formula%20you%20present%20the%20result%20in%20another%20column.%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20said%2C%20I%20think%20you%20should%20consider%20how%20you%20have%20your%20sheet%20structured.%26nbsp%3B%20If%20this%20is%20data%20entry%20then%20structure%20the%20sheet%20so%20it%20is%20entered%20in%20corresponding%20columns.%26nbsp%3B%20If%20you%20are%20importing%20data%2C%20then%20use%20power%20query%20or%20something%20to%20help.%26nbsp%3B%20IMHO%2C%20I%20think%20you%20need%20to%20step%20back%20and%20look%20at%20your%20situation%20from%20another%20angle.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello!

 

My objective is to create a custom number format that can work for phone numbers AND dates inside the same cell. 

 

EXAMPLE:

JohnnyB12_0-1611062472660.png

 

On the leftmost cell, I've applied the following custom number format: (###) ###-####

However, this format is not applicable on the rightmost cell with text because aside from having phone numbers, it also includes dates and words. What formula can be used to have all phone numbers in this format: (###) ###-### and also the date in this format: mm/dd/yy, while keeping text as is? (In this order as well: phone number, date, note)

 

Any help is greatly valued!! Thank you!

2 Replies

@JohnnyB12 

You can use a formula such as

 

="HOUSE NOTES: PHONE NOTES: "&TEXT(A2,"(###) ###-####")

@JohnnyB12 A custom number format doesn't have that capability.  You would have to use a macro to scan any/all text entered and modify according to your rules or you could use helper columns so you enter it in 1 column but using a sophisticated formula you present the result in another column. 

That said, I think you should consider how you have your sheet structured.  If this is data entry then structure the sheet so it is entered in corresponding columns.  If you are importing data, then use power query or something to help.  IMHO, I think you need to step back and look at your situation from another angle.