Trimming all spaces
Hi,
I am trying to apply a formula so that when meta data is input it is automatically trimmed to have zero spaces, which I know how to do in excel but does not seem to work in smartsheet.
So, I have a column where we input license plates, but people always add spaces making sorting the column a little annoying as duplicates won't sort properly if there are spaces in one and not in the other.
For example:
If someone inputs the license plate as 'ABC 123' with a space between ABC and 123, I want that space to automatically trim so the plate now reads 'ABC123'
The formula I tried comes out as circular. This is what I have tried:
=SUBSTITUTE([License Plate / Unit Number]1, " ", "")
Thanks if anyone is able to help
Best Answer
-
I noticed the error now. You're referencing the same cell that contains the formula, which won't work.
You'll have to add a so-called helper column with the formula.
Make sense?
Would that work?
✅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.
Answers
-
Hi @BradJoseph
Try something like this.
=SUBSTITUTE([License Plate / Unit Number]@row, " ", "")
Did that work/help?
I hope that helps!
Be safe and have a fantastic weekend!
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.
-
Still says #circular reference
-
I assume you have another formula causing the Circular Reference. Correct?
I'd be happy to take a quick look.
Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)
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.
-
There are zero other formulas in the sheet, its a brand new sheet and this is the first formula I am trying to get into it.
I just want for info entered into a certain column to not have spaces, ABC 123 would become ABC123, no space between ABC and 123.
I am fairly new to smartsheet, but I am not to this type of program, just so you know.
-
I see my formula above has a space between the second quote marks, but it makes no difference if it does not.
-
The formula has to be as mine with the space on the last part because that's what removes the space(s).
Regarding the error.
Please double-check this.
#CIRCULAR REFERENCE
Cause
The formula references itself. The circular reference may be direct where the reference is in the formula text itself, or indirect where this formula references a cell which then references back to this cell.
Resolution
Determine which reference is circular. Indirect references can be many levels deep. Sometimes it is easiest to make a copy of the formula and remove cell references until the error is eliminated. This process of elimination will help you see which reference is ultimately circular.
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.
-
It is, that why I said in the picture above the space was an accident but it does not make any difference if it is there or not it still says circular reference
-
Yes, that part doesn't affect the Circular Reference error.
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.
-
So what is?
It is the only formula in that column, and the rest of the sheet only has conditional formatting which doesn't actually affect that column at all.
-
Ensure that there aren't any other formulas, and maybe clear/delete the cells to be 100% sure.
Did you find any formulas?
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.
-
There are zero formulas, this is the first formula that I have entered into the sheet ever. Brand new sheet.
-
That's strange! Can you share the sheet or a copy of it with me so that I can take a quick look?
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.
-
Please send/share it to andree@workbold.com
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.
-
I noticed the error now. You're referencing the same cell that contains the formula, which won't work.
You'll have to add a so-called helper column with the formula.
Make sense?
Would that work?
✅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.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!