If a cell has a value, display text?

Question

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?

Answer 1

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.

Answer 2

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.