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)

Bassam Khalil

Hi @Colette Hughes 

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Colette Hughes

Hi,

This is a screenshot of what I am trying to do if that helps.

Bassam Khalil

@Colette Hughes 

Please try to use the Join function instead of + because when you use + and you add empty cells the system will take 0 for that cells.

Colette Hughes

I have tried the join function too but that has made no difference

Bassam Khalil

@Colette Hughes 

Could you please share me as an admin on a sample sheet (after removing or replacing any sensitive information).

My Email : Bassam.k@mobileprojectit

Colette Hughes

It won't let me share, says the email address is not valid. If it helps, this is the formula I am using and I now get the message #unparseable

Bassam Khalil

Or you can use the following formula to remove the 0

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

Bassam Khalil

@Colette Hughes

Sorry i this is the correct Email

bassam.k@mobilproject.it

please share

Bassam Khalil

@Colette Hughes 

your formula needs correction.

 this is the correct Email

bassam.k@mobilproject.it

please share

Andrée Starå

Hi @Colette Hughes

I hope you're well and safe!

Try something like this.

=JOIN(COLLECT([DVC(A) Department]@row:School@row, [DVC(A) Department]@row:School@row, <>""), "-")

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Colette Hughes

Now the drop down options in two of the columns omit the first letter of the selection. Any ideas why?

Andrée Starå

@Colette Hughes

Strange!

Not with my method, I assume?

Colette Hughes

Thanks Andree, I used the MID method and had the issue but by using your method it fixed the issue.

Andrée Starå

@Colette Hughes

Excellent!

You're more than welcome!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!