Merging Cells

[Harry Hornet]

I have a names and address file that has house numbers and street names in
separate cells.
I need to merge them so that they are in the same cell with a space between
them.
I have used CONCATENATE and I have had success with the first record.
However when I try to use the fill handle to populate down the rest of the
records, the same result is returned.
The formula in the cell is correct and if I click on it then the function
symbol, the correct result is then displayed.
I am not sure if I have explained this very well but if someone can help I
would be grateful.

 

[Tom Hutchins]

It sounds like your formula has absolute references and needs relative
references. Absolute references are shown by dollar signs before the column
and/or row references within the formula. Remove the dollar signs and try
filling down again.

Hope this helps,

Hutch

 

[RagDyer]

Sounds like you created the formula in a cell that was formatted to text.

Select the column of "bad" formulas, then, from the Menu Bar,
Just open and close TTC,
<Data> <TextToColumns> <Finish>

This gives you working formulas.

While they're still selected, format them to General if you wish.

 

[Ken Johnson]

I have a names and address file that has house numbers and street names in
separate cells.
I need to merge them so that they are in the same cell with a space between
them.
I have used CONCATENATE and I have had success with the first record.
However when I try to use the fill handle to populate down the rest of the
records, the same result is returned.
The formula in the cell is correct and if I click on it then the function
symbol, the correct result is then displayed.
I am not sure if I have explained this very well but if someone can help I
would be grateful.

Also sounds like your Calculation mode is not set on Automatic. Try
Tools|Options|Calculation Tab then click on Automatic option.

Ken Johnson

 

[Harry Hornet]

Brilliant, Thanks for your help.

Also sounds like your Calculation mode is not set on Automatic. Try
Tools|Options|Calculation Tab then click on Automatic option.

Ken Johnson

 

[Mary-Anne]

Hi Harry, I want to do that too! What is your formula please?

 

[Gord Dibben]

=A1 & " " & B1 & " " & C1 & " " & D1

Select the four cells and drag/copy down.


Gord Dibben MS Excel MVP

 

[EngelseBoer]

Hi Mary-Anne
relise you never got a reply to this and was huntingfor the same resolution
found one
formula =
=CONCATENATE(A1,B1,C1) etc FOR ROWS
or =CONCATENATE(A1,A2,A3) ets FOR COLUMN

OR ENTER RANDOM CELL NUMBER AT YOUR LEISURE

 

[Mary-Anne]

Thank you all. It works like a charm. One small problem. If I search for
an item in that concatenated cell, I find that Excel cannot find that item.
eg if the word french is in the cell and I search fro french in the worksheet
it does not go to that cell at all. I wonder why?

 

[EngelseBoer]

that is because the cells true value is an equation
when i use these functions, i have to copy all and paste to a new book as
"values"
then all these entities are findable

 

[EngelseBoer]

you could just do similar in the same work book - sheet - cell - column - row
-- or what ever pleases you -- ie copy and paste special "values"
all depends on your needs
for me i need the math so work in 2 workbooks
one with the calculations one as values
(see my previous response)

 

[Mary-Anne]

Thank you! That is very helpful. Works well.