Concatenate or "&" problem

[Guest]

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!

 

[Niek Otten]

What formulas? What input values?

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Niek Otten]

I can't see anything wrong with your formula. Start with just =A1. If that
does not return an error, try =A1&" ". etc.

 

[Guest]

Like Niek, I can't find anything wrong either, and I can't get it to
duplicate the error code. I've even tried formatting the number 123 as a
number, as currency, as a date, etc, and it is still working fine for me.
Must be one of those computer glitches or something.

The only other thing I could suggest to try is to copy all the info in your
spreadsheet, and paste it into a brand new excel document as text only, and
then manually input your formula again. Sometimes that has worked for me
when I've had an unexplainable glitch.

Hope you get it worked out.

(PS, if you don't have all four columns filled in for every record, you can
use the following formula: =A1&(IF(B1<>""," ",""))&B1&(IF(C1<>"","
",""))&C1&(IF(D1<>""," ",""))&D1 to erase the double spacing between words.)