How to remove leading zero's in concatenated cells

Colette Hughes

I have a series of fields in a sheet, of which only one of those fields is completed each row. I need to combine that series of fields into one field so I don't need to scroll across the sheet.

I have tried using the "=" function so for example

=[Option1]@row+[Option2]@row+[Option3]@row

This works and populates correctly in terms of whichever of those cells holds data the final destination cell will show that data. However, it shows with a leading zero, for example

0Option2

How can I get rid of that leading 0 in the destination cell?

Bassam Khalil

Or you can use the following formula to remove the 0

=MID([Option1]@row+[Option2]@row+[Option3]@row,2,1000)