1

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?

1
  • 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?
    – Erik Tyler
    Apr 14 at 4:13

2 Answers 2

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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.