Trimming all spaces

BradJoseph

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

Andrée Starå

@BradJoseph

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!

Andrée Starå

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!

BradJoseph

Still says #circular reference

Andrée Starå

@BradJoseph

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)

BradJoseph

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.

BradJoseph

I see my formula above has a space between the second quote marks, but it makes no difference if it does not.

Andrée Starå

@BradJoseph

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.

BradJoseph

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

Andrée Starå

@BradJoseph

Yes, that part doesn't affect the Circular Reference error.

BradJoseph

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.

Andrée Starå

@BradJoseph

Ensure that there aren't any other formulas, and maybe clear/delete the cells to be 100% sure.

Did you find any formulas?

BradJoseph

There are zero formulas, this is the first formula that I have entered into the sheet ever. Brand new sheet.

Andrée Starå

@BradJoseph

That's strange! Can you share the sheet or a copy of it with me so that I can take a quick look?

BradJoseph

https://app.smartsheet.com/sheets/jRRrCfv6WVwhFr95x4xqfcG78Gh2QxPpg5gFWCx1

Andrée Starå

@BradJoseph

Please send/share it to andree@workbold.com

Andrée Starå

@BradJoseph

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!