SOLVED

=A1&" "&B1&" "&C1&" "&D1&" "&E1&" "&F1&" "&G1&" "&H1&" "&I1&" "&J1&" "&K1 then series fill

%3CLINGO-SUB%20id%3D%22lingo-sub-2679182%22%20slang%3D%22en-US%22%3E%3DA1%26amp%3B%22%20%22%26amp%3BB1%26amp%3B%22%20%22%26amp%3BC1%26amp%3B%22%20%22%26amp%3BD1%26amp%3B%22%20%22%26amp%3BE1%26amp%3B%22%20%22%26amp%3BF1%26amp%3B%22%20%22%26amp%3BG1%26amp%3B%22%20%22%26amp%3BH1%26amp%3B%22%20%22%26amp%3BI1%26amp%3B%22%20%22%26amp%3BJ1%26amp%3B%22%20%22%26amp%3BK1%20then%20series%20fill%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2679182%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20the%20formula%20above.%20Some%20of%20the%20columns%20are%20dates%20ex%208%2F19%3C%2FP%3E%3CP%3EHowever%2C%20the%20excel%20is%20merging%20them%20into%20a%20nonsense%20number%20that%20is%20not%20a%20date.%3C%2FP%3E%3CP%3ECan%20you%20tell%20me%20how%20to%20tell%20excel%20to%20keep%20the%20date%20format%20in%20the%20'merged'%20cell%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2679182%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2679289%22%20slang%3D%22en-US%22%3ERe%3A%20%3DA1%26amp%3B%22%20%22%26amp%3BB1%26amp%3B%22%20%22%26amp%3BC1%26amp%3B%22%20%22%26amp%3BD1%26amp%3B%22%20%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2679289%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1136222%22%20target%3D%22_blank%22%3E%40Pamela_H%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20the%20cell%20that%20you%20need%20to%20put%20a%20date%20in%2C%20it%20has%20to%20be%20in%20the%20form%20of%20the%20TEXT%20formula.%20The%20date%20format%20can%20be%20whatever%20you%20select%20in%20Format%20window.%26nbsp%3B%20(Image%20below)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20for%20you%20example.%26nbsp%3B%20Please%20see%20function%20below.%20I%20wanted%20the%20date%20format%20to%20be%20mm-dd-yyyy%20then%20added%20that%20in%20quotes%20as%20part%20of%26nbsp%3B%20the%20TEXT%20Function.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20the%20result%20is%20the%20following%3A%26nbsp%3B1%202%20Sample%20text%205%206%20Sample%2008-24-2021%20Test%20Sample%20Test%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DA1%26amp%3B%22%20%22%26amp%3BB1%26amp%3B%22%20%22%26amp%3BC1%26amp%3B%22%20%22%26amp%3BD1%26amp%3B%22%20%22%26amp%3BE1%26amp%3B%22%20%22%26amp%3BF1%26amp%3B%22%20%22%26amp%3BG1%26amp%3B%22%20%22%26amp%3BTEXT(H1%2C%22%5B%24-en-US%5Dmm-dd-yyyy%3B%40%22)%26amp%3B%22%20%22%26amp%3BI1%26amp%3B%22%20%22%26amp%3BJ1%26amp%3B%22%20%22%26amp%3BK1%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20image-alt%3D%22ExcelExampleTextFunction.JPG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F305372i2A4E0B32E218E16B%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22ExcelExampleTextFunction.JPG%22%20alt%3D%22ExcelExampleTextFunction.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello,

 

I am using the formula above. Some of the columns are dates ex 8/19

However, the excel is merging them into a nonsense number that is not a date.

Can you tell me how to tell excel to keep the date format in the 'merged' cell

2 Replies
best response confirmed by Pamela_H (New Contributor)
Solution

@Pamela_H 

For the cell that you need to put a date in, it has to be in the form of the TEXT formula. The date format can be whatever you select in Format window.  (Image below)

 

So for you example.  Please see function below. I wanted the date format to be mm-dd-yyyy then added that in quotes as part of  the TEXT Function.  

 

and the result is the following: 1 2 Sample text 5 6 Sample 08-24-2021 Test Sample Test

 

=A1&" "&B1&" "&C1&" "&D1&" "&E1&" "&F1&" "&G1&" "&TEXT(H1,"[$-en-US]mm-dd-yyyy;@")&" "&I1&" "&J1&" "&K1

 

ExcelExampleTextFunction.JPG

 

Thank you, Southern Gentleman!
I am grateful you took the time to respond!