How to remove leading zero's in concatenated cells
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?
Best Answer
-
Or you can use the following formula to remove the 0
=MID([Option1]@row+[Option2]@row+[Option3]@row,2,1000)
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
Answers
-
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.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Hi,
This is a screenshot of what I am trying to do if that helps.
-
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.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
I have tried the join function too but that has made no difference
-
Could you please share me as an admin on a sample sheet (after removing or replacing any sensitive information).
My Email : Bassam.k@mobileprojectit
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
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
-
Or you can use the following formula to remove the 0
=MID([Option1]@row+[Option2]@row+[Option3]@row,2,1000)
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
your formula needs correction.
this is the correct Email
bassam.k@mobilproject.it
please share
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Now the drop down options in two of the columns omit the first letter of the selection. Any ideas why?
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Thanks Andree, I used the MID method and had the issue but by using your method it fixed the issue.
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!