Zip+4 not showing the dash

%3CLINGO-SUB%20id%3D%22lingo-sub-2676958%22%20slang%3D%22en-US%22%3EZip%2B4%20not%20showing%20the%20dash%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2676958%22%20slang%3D%22en-US%22%3E%3CP%3Ehello.%20I%20combined%20two%20columns%20using%20the%20%3DCONCAT%20function.%20The%20two%20columns%20were%20zip%20code%20and%20then%20the%20Plus%204%20digits.%26nbsp%3B%20I%20then%20highlighted%20the%20new%20column%20to%20change%20it%20to%20the%20Special%20Text%20Zip%2B4.%20However%2C%20the%20dash%20does%20not%20show%20unless%20I%20go%20into%20each%20cell%20and%20then%20click%20Enter.%26nbsp%3B%20There%20are%20too%20many%20rows%20to%20do%20this%20manually.%20Is%20there%20a%20shortcut%20to%20do%20this%3F%20I%20attached%20a%20screenshot%20of%20what%20it%20looks%20like.%20as%20of%20now.%26nbsp%3B%20I%20did%20try%20to%20create%20a%20Macro%2C%20which%20I%20have%20never%20used%20before%2C%20but%20that%20didn't%20work%20for%20me%20(probably%20because%20I%20don't%20know%20what%20I'm%20doing.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2676958%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2676993%22%20slang%3D%22en-US%22%3ERe%3A%20Zip%2B4%20not%20showing%20the%20dash%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2676993%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1135607%22%20target%3D%22_blank%22%3E%40jackiebiggs%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20since%20CONCAT()%20returns%20text%2C%20not%20number%2C%20and%20you%20shall%20re-enter%20after%20changing%20the%20type.%3C%2FP%3E%0A%3CP%3EYou%20may%20change%20formula%20on%20O1%20from%20%3DCONCAT(A1%2CB1)%20on%20%3DCONCAT(A1%2CB1)%2B0%3C%2FP%3E%0A%3CP%3EApply%20ZIP%2B4%20format%20to%20column%20O%2C%20select%20entire%20range%20(e.g.%20O1%3AO1000)%20and%20Ctrl%2BD.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAlternatively%20use%20texts%20like%20%3DA1%20%26amp%3B%20%22-%22%20%26amp%3B%20B1%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2677049%22%20slang%3D%22en-US%22%3ERe%3A%20Zip%2B4%20not%20showing%20the%20dash%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2677049%22%20slang%3D%22en-US%22%3EThanks%20Sergei.%20I%20will%20try%20what%20you%20suggested.%3C%2FLINGO-BODY%3E
New Contributor

hello. I combined two columns using the =CONCAT function. The two columns were zip code and then the Plus 4 digits.  I then highlighted the new column to change it to the Special Text Zip+4. However, the dash does not show unless I go into each cell and then click Enter.  There are too many rows to do this manually. Is there a shortcut to do this? I attached a screenshot of what it looks like. as of now.  I did try to create a Macro, which I have never used before, but that didn't work for me (probably because I don't know what I'm doing.

 

Thanks in advance!

4 Replies

@jackiebiggs 

That since CONCAT() returns text, not number, and you shall re-enter after changing the type.

You may change formula on O1 from =CONCAT(A1,B1) on =CONCAT(A1,B1)+0

Apply ZIP+4 format to column O, select entire range (e.g. O1:O1000) and Ctrl+D.

 

Alternatively use texts like =A1 & "-" & B1

Thanks Sergei. I will try what you suggested.
Thanks so much Sergie. It worked!

@jackiebiggs , you are welcome, glad it helped