# If a cell has a value, display text?

I'm trying to display text in a cell if another cell has a value. I can do this successfully, however it adds an extra space that I can't have

One column has a unit number, the next column has an address field. I am combining the 2 together with a - in between e.g.

• Column M has the unit (e.g. 123
• Column N has the address (e.g 12345 Sample St)
• Column O has the formula =if(M1, "-",)

Column F has `=M1&""&O1 &N1`

The result is

• 123 -12345 Sample St
without the entry in column M the result also has a space in front of the 1234 Sample St
I'm trying to achieve a result without a space between 123 and 12345 Sample St (123-12345 Sample St)

How would I do this?

• Your post says that address entries begin in Row 1, whereas Row 1 typically had header text. What is the actual case in your sheet? Apr 14 at 4:13

Try this: `=M1&O1&N1`

There's no need for the quotation marks in your existing formula, and I think this is where the error is creeping in.

TROUBLESHOOTING

• `=len(M1)` - the result should be 3
• `=len(N1)` - the result should be 15
• `=len(O1)` - the result should be 1

The total length of `=M1&O1&N1` should be 19.

See my comment to your original post. For now, I'll assume that you do have a header in F1 and that your actual addresses begin in Row 2.

Select Col F and delete everything (including the header).

In F1, place the following formula:

`=ArrayFormula({"Full Street Address"; IF(N2:N="",,IF(M2:M="",,TRIM(M2:M)&"-")&TRIM(N2:N))})`

You don't need Col O at all.

This one formula should produce the header (which you can within the formula itself as you like) and all results for all rows.