For example, if I have the address 123 N Main St in four separate cells, I
used the formula =A1&" "&B1&" "&C1&" "&D1 to get it all in one cell, but I
always get the same error code of #VALUE!

I have tried to format everything as text, with no luck. They all started
out as general formatting. I know it's a formatting problem, because if I
just try to combine the last three cells it works fine. When I try to add the
numbered cell to the formula, it won't work. I've tried to re-format, copy
the cells over as text, everything I can think of.

I have done this before in other spreadsheets with no problem.

Dino


"C. Cunningham" wrote:

Hi there,

I've used the "&" function quite extensively for various projects, so I
might be able to help. I do know that any cells that you want to use must be
formatted as "general" or as "text". Any number formatting will not work and
will generate an error message.

Let's assume your number is in column A, street name in column B and street
type in column C

A2 B2 C2
1234 Test Street

The formula to combine the 3 would be =A2&" "&B2&" "&C2
giving you the result of: 1234 Test Street

If this is already what you are doing, then it might be a problem with the
formatting. I've had trouble sometimes with Excel not really changing
numbers over to general format, once they are numbers, it can be hard to
change.

Hope this helps, if not, we might need more specifics.

(PS, you can also do modified "If, Then, Else" statements within the &
function. They work quite nicely to be sure you don't have double spaces,
say between the number and street name if there is no direction.)

C. Cunningham


"Dino" wrote:

I have tried to concatenate or use the "&" in my database with no luck so
far. I get either the #VALUE! or #NAME? result. I have addresses that are
separated into columns by number, direction, street, and suite. I want to
combine the numbers and text onto one line, in one column. I've tried
reformatting everything into text or numbers, with no luck. I have done this
in previous databases before, with no problems. Hopefully someone can help!