SOLVED

Excel Combing Data Cells

%3CLINGO-SUB%20id%3D%22lingo-sub-325841%22%20slang%3D%22en-US%22%3EExcel%20Combing%20Data%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-325841%22%20slang%3D%22en-US%22%3E%3CP%3Ewhat's%20the%20formula%20to%20combine%20more%20then%20(2)%20text%20data%20cells%3F%3C%2FP%3E%3CP%3Especifically%20I%20want%20to%20combine%20cells%20E2%20%3A%20H2%20%3A%20L2%20%3A%20P2%20into%20a%20new%20cell%3C%2FP%3E%3CP%3E%3DE2%26amp%3B%22%20%22%26amp%3BH2%20%22%26amp%3BL2%20%22%26amp%3BP2%20does%20not%20yield%20what%20I%20want%3C%2FP%3E%3CP%3EPlease%20advise%20the%20proper%20forumla%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-325841%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-326568%22%20slang%3D%22en-US%22%3ERE%3A%20Excel%20Combing%20Data%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-326568%22%20slang%3D%22en-US%22%3EI%20need%20the%20numbers%20in%20the%20cell%20A1%20through%20A17%20to%20be%20math%20processed%20and%20copied%20to%20cells%20N1%20through%20N17%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-326545%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Combing%20Data%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-326545%22%20slang%3D%22en-US%22%3ETY%20%3D%20works%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-326101%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Combing%20Data%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-326101%22%20slang%3D%22en-US%22%3E%3CP%3EIt%20depends%20on%20what%20do%20you%20want%20as%20result.%20Your%20formula%20doesn't%20work%20at%20all%2C%20you%20may%20use%20CONCATENATE%20as%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F272376%22%20target%3D%22_blank%22%3E%40jmcl0000%3C%2FA%3E%26nbsp%3Bposted%20or%20correct%20your%20formula%20to%3C%2FP%3E%0A%3CPRE%3E%3DE2%20%26amp%3B%22%20%22%20%26amp%3B%20H2%20%26amp%3B%20%22%20%22%20%26amp%3B%20L2%20%26amp%3B%20%22%20%22%20%26amp%3B%20P2%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-325909%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Combing%20Data%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-325909%22%20slang%3D%22en-US%22%3E%3CP%3E%3Dconcatenate(a1%2C%22%20%22%2Cf1%2C%22%20%22%2Ck1%2C%22%20%22%2C%20t1)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1977213%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Combing%20Data%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1977213%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F272376%22%20target%3D%22_blank%22%3E%40jmcl0000%3C%2FA%3E%26nbsp%3B%20here%20are%20the%20two%20formulas%20I%20am%20using%20to%20try%20to%26nbsp%3B%20combine%20the%20first%20and%20last%20name%20in%20two%20separate%20columns%20into%20one%20column.%26nbsp%3B%20Neither%20works%20and%20only%20shows%20the%20formula%20in%20the%20cell.%26nbsp%3B%20The%20formula%20is%20not%20executing.%26nbsp%3B%20I%20have%20tried%20spacing%20as%20shown%20in%20another%20answer%20as%20well%20and%20the%20result%20is%20the%20same.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3Dconcatenate(e2%22%20%22f2)%3C%2FP%3E%3CP%3E%3Dconcat(e2%22%20%22f2)%3C%2FP%3E%3CP%3E%3DE3%20%26amp%3B%22%20%22%20%26amp%3B%20F3%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1977295%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Combing%20Data%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1977295%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F897957%22%20target%3D%22_blank%22%3E%40McGeheeWoolf%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlease%20check%20if%20Show%20Formulas%20is%20switched%20off%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20677px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F239648i4187EF1626D39901%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1978095%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Combing%20Data%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1978095%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%3C%2FP%3E%3CP%3EI%20clicked%20on%20Show%20Formulas%26nbsp%3B%20several%20times%20and%20the%20result%20is%20no%20change.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1978681%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Combing%20Data%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1978681%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F897957%22%20target%3D%22_blank%22%3E%40McGeheeWoolf%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20works%20as%20a%20trigger%2C%20each%20time%20you%20click%20on%20it%20it%20changes%20behavior%20of%20the%20formulas%20entered.%20If%20%3CSTRONG%3EShow%20formulas%3C%2FSTRONG%3E%20highlighted%2C%20it%20shows%20formulas%2C%20otherwise%20result%20of%20calculations.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnother%20variant%20is%20that%20cells%20into%20which%20you%20enter%20formulas%20are%20formatted%20as%20texts.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20it%20could%20be%20more%20exotic%20cases%2C%20it%20takes%20time%20to%20make%20all%20guesses.%20Small%20sample%20file%20instead%20of%20PDF%20could%20shorten%20this%20discussion%20dramatically.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

what's the formula to combine more then (2) text data cells?

specifically I want to combine cells E2 : H2 : L2 : P2 into a new cell

=E2&" "&H2 "&L2 "&P2 does not yield what I want

Please advise the proper forumla

15 Replies
best response confirmed by wtmd (New Contributor)
Solution

=concatenate(a1," ",f1," ",k1," ", t1)

It depends on what do you want as result. Your formula doesn't work at all, you may use CONCATENATE as @jmcl0000 posted or correct your formula to

=E2 &" " & H2 & " " & L2 & " " & P2

 

TY = works
I need the numbers in the cell A1 through A17 to be math processed and copied to cells N1 through N17

@jmcl0000  here are the two formulas I am using to try to  combine the first and last name in two separate columns into one column.  Neither works and only shows the formula in the cell.  The formula is not executing.  I have tried spacing as shown in another answer as well and the result is the same.  

 

=concatenate(e2" "f2)

=concat(e2" "f2)

=E3 &" " & F3

 

@McGeheeWoolf 

Please check if Show Formulas is switched off

image.png

@Sergei Baklan

I clicked on Show Formulas  several times and the result is no change.

@McGeheeWoolf 

It works as a trigger, each time you click on it it changes behavior of the formulas entered. If Show formulas highlighted, it shows formulas, otherwise result of calculations. 

 

Another variant is that cells into which you enter formulas are formatted as texts.

 

Perhaps it could be more exotic cases, it takes time to make all guesses. Small sample file instead of PDF could shorten this discussion dramatically.

@Sergei Baklan 

Thanks so much for the assistance.  I want to combine the first and last name in columns E and F respectively with the result in column D.  First name first.

 

See attached.

@McGeheeWoolf 

Cells in this column are formatted as text

image.png

thus all what you enter is considered as text, includes formulas. You have text of formulas.

You need to apply General format (select entire column and on ribbon apply it from Home->Number section). After that re-enter formulas.

Both

=CONCATENATE(E2," ",F2)
and
=E3 &" " & F3

work. Please check in attached file.

@Sergei Baklan 

That worked!  Thanks so much Sergei.  Have a great weekend.

@McGeheeWoolf 

Is there a way after the first and last names are combined that I can somehow change the formatting of the eliminate the two source columns (E and F) and have the result remain?

@McGeheeWoolf 

- select resulting column

- copy

- paste->paste values

- remove source columns

@Sergei Baklan 

Worked beautifully.  Thanks again!

@McGeheeWoolf , you are welcome