Merging text from various cells into one cell

  • Thread starter Thread starter Mon
  • Start date Start date
M

Mon

I need some help urgently!:confused:

I have an Excel table where all the text (each sentence) is in a
different cell and all in Column B. There are empty cells too to
separate out the sections of the text.

I want to merge these sections of text into one cell each e.g. there
will be 5 cells of text then an empty cell, then another 5 cells of
text, etc... I want to merge the text in the first 5 cells into one
cell, then merge the text of the next 5 cells into one cell. I know
that using the formula CONCATENATE will merge the text, but when I
click on the merged text, all I see is the formula. I need to be able
to see the text normally. Is there any way of doing this?

Plus, as the Excel is very long, isn't there a way of automating this
process? That is to say, do I have to go down the Excel sheet and
select the five cells to concatenate, then the next five cells to
concatenate, etc... Is there any way of doing this automatically?

Thank you in advance!!!!

Mon
 
Cell F1 =CONCATENATE(A1," ",B1," ",C1," ",D1," ",E1

Cell L1 =CONCATENATE(G1," ",H1," ",I1," ",J1," ",K1

Drag each column down to the bottom of the list

Hi-lite each column, copy/paste special values
Hi-lite each column, CNTR H, replace double spac
with single. (Should get rid of extra spaces
 
1. Put this formula into another column (Here column C starting at C2)
and copy down. You need at least one blank cell above the first
formula.
=IF(B2="","",C1&B2). You can copy/paste from here to the formula bar
using Ctrl+C/Ctrl+V.

2. The only way of seeing text in the formula bar is to have text in
the cells. You can copy your column containing the formulas and either
Edit/Paste Special/Values back into the same column, or another column
if you do not wish to spoil your formulas.
 
Sorry, I did not read the first part of the question properly
I thought the cells were in two groups 5 across (Horizotal
Obviously the method I suggested would not work.
 
Hi BrianB,

Thanks for the message!
I've tried to do what you suggested but the formula you gave me keeps
appearing as not valid when I try to insert it into the formula
section.

Maybe I am doing it incorrectly:
I make sure I have selected the cell C2 and that the Excel sheet is in
Formula Mode (in the menu "Tools").
I then click on -fx- and choose Concatenate.
Then I insert the formula you have sent me. As soon as I insert it, it
automatically says not valid in red letters.

Can you tell me if I'm doing this correctly? Do I insert your formula
next to every line of text (changing C2 to whatever cell number it
should correspond to)?

Thanks BrianB,

Kind regards,
Mon
 
=IF(B2="","",C1&B2)

This is the formula. You may have included the full stop in error.
Copy it into Cell C2.

Select c2 with formula in it. menu Edit/Copy
Select empty cells below c2 (C3 downwards) Edit/Paste
special/Formulas.

when it will change accordingly. don't know what you mean by formula
mode. no need for anything special.
 
Back
Top