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.

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

"Harry Hornet" wrote:

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.

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.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------.


"Harry Hornet" wrote in message
...
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.

On Sep 12, 7:12*am, Harry Hornet
wrote:
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

Brilliant, Thanks for your help.

"Ken Johnson" wrote:

On Sep 12, 7:12 am, Harry Hornet
wrote:
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

Hi Harry, I want to do that too! What is your formula please?
--
Mary-Anne in Durban


"Harry Hornet" wrote:

Brilliant, Thanks for your help.

"Ken Johnson" wrote:

On Sep 12, 7:12 am, Harry Hornet
wrote:
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

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

Select the four cells and drag/copy down.


Gord Dibben MS Excel MVP

On Fri, 12 Sep 2008 06:09:02 -0700, Mary-Anne
wrote:

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

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" wrote:

Hi Harry, I want to do that too! What is your formula please?
--
Mary-Anne in Durban


"Harry Hornet" wrote:

Brilliant, Thanks for your help.

"Ken Johnson" wrote:

On Sep 12, 7:12 am, Harry Hornet
wrote:
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

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?
--
Mary-Anne in Durban


"EngelseBoer" wrote:

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" wrote:

Hi Harry, I want to do that too! What is your formula please?
--
Mary-Anne in Durban


"Harry Hornet" wrote:

Brilliant, Thanks for your help.

"Ken Johnson" wrote:

On Sep 12, 7:12 am, Harry Hornet
wrote:
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

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

"Mary-Anne" wrote:

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?
--
Mary-Anne in Durban


"EngelseBoer" wrote:

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" wrote:

Hi Harry, I want to do that too! What is your formula please?
--
Mary-Anne in Durban


"Harry Hornet" wrote:

Brilliant, Thanks for your help.

"Ken Johnson" wrote:

On Sep 12, 7:12 am, Harry Hornet
wrote:
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

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" wrote:

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?
--
Mary-Anne in Durban


"EngelseBoer" wrote:

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" wrote:

Hi Harry, I want to do that too! What is your formula please?
--
Mary-Anne in Durban


"Harry Hornet" wrote:

Brilliant, Thanks for your help.

"Ken Johnson" wrote:

On Sep 12, 7:12 am, Harry Hornet
wrote:
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

Thank you! That is very helpful. Works well.
--
Mary-Anne in Durban


"EngelseBoer" wrote:

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" wrote:

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?
--
Mary-Anne in Durban


"EngelseBoer" wrote:

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" wrote:

Hi Harry, I want to do that too! What is your formula please?
--
Mary-Anne in Durban


"Harry Hornet" wrote:

Brilliant, Thanks for your help.

"Ken Johnson" wrote:

On Sep 12, 7:12 am, Harry Hornet
wrote:
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