Is it possible to use the LEFT function across multiple columns?

David White

I am building a UAT smartsheet that is working really well. However, i want to calculate the tests performed using the test reference i have created for each test. I have successfully used this formula for a single column, BUT need to have it work across multiple columns, does anyone know if this can be done, and if so, whats wrong with my formula?

This formula works, it pulls the first 7 characters from the row within this column

=LEFT([Accounts Receivable Test Reference #]@row, 7)

I want to expand this across multiple columns, using this below, but it is unparseable, any suggestions on where i am going wrong?

=LEFT([Bank Reconciliation Test Reference #]:[AT Statement Test Reference #]@row, 7)

Thank you

Nick Korna

Hi @David White,

Unfortunately, the LEFT function doesn't quite work that way. Are you trying to have the leftmost 7 characters pulled from multiple columns or just the latest one in a sequence (and I'm assuming the rightmost one on a row)?

If the former it would just be =LEFT([First column]@row, 7) + LEFT([Second column]@row,7) + (etc.).

If the latter you'd need a nested statement, but as I don't know what your column headers are it's difficult to write this blind - if you can paste these in then I can provide more help.

Nick Korna

@David White ,

No doubt there is a more elegant way to do this, but this formula should work:

=IF(ISBLANK([Bank Reconciliation Test Reference #]@row), "", LEFT([Bank Reconciliation Test Reference #]@row, 7)) + IF(ISBLANK([Accounts Receivable Test Reference #]@row), "", LEFT([Accounts Receivable Test Reference #]@row, 7)) + IF(ISBLANK([Fixed Assets Test Reference #]@row), "", LEFT([Fixed Assets Test Reference #]@row, 7)) + IF(ISBLANK([General Ledger Test Reference #]@row), "", LEFT([General Ledger Test Reference #]@row, 7)) + IF(ISBLANK([Supply Chain - Inventory Test Reference #]@row), "", LEFT([Supply Chain - Inventory Test Reference #]@row, 7)) + IF(ISBLANK([Supply Chain - Purchasing Test Reference #]@row), "", LEFT([Supply Chain - Purchasing Test Reference #]@row, 7)) + IF(ISBLANK([Supply Chain - System Admin Test Reference #]@row), "", LEFT([Supply Chain - System Admin Test Reference #]@row, 7)) + IF(ISBLANK([AT Statement Test Reference #]@row), "", LEFT([AT Statement Test Reference #]@row, 7)) + IF(ISBLANK([Prosthesis Test Reference #]@row), "", LEFT([Prosthesis Test Reference #]@row, 7)) + IF(ISBLANK([Accounts Payable Test Reference #]@row), "", (LEFT([Accounts Payable Test Reference #]@row, 7)))

I hope I have your column names correct; it not I hope you get the gist of what needs doing!

Nick Korna

Hi @David White,

Unfortunately, the LEFT function doesn't quite work that way. Are you trying to have the leftmost 7 characters pulled from multiple columns or just the latest one in a sequence (and I'm assuming the rightmost one on a row)?

If the former it would just be =LEFT([First column]@row, 7) + LEFT([Second column]@row,7) + (etc.).

If the latter you'd need a nested statement, but as I don't know what your column headers are it's difficult to write this blind - if you can paste these in then I can provide more help.

David White

Hi Nick,

The former is correct.

=LEFT([First column]@row, 7) + LEFT([Second column]@row,7) + (etc.).

I will build this out, IF this doesnt work, i will be back :)

Thank you

David

David White

Hi @Nick Korna

I tried the above formula, starting with 2 columns, but still get unparseable.

The screen shot below is what i am trying to achieve, I have a number of columns, that i want the first 7 digits in those columns to be brought into the column labelled "code".

I can get it working for 1 column, i cant get it to work for multi columns.

I hope this is a little clearer.

Thank you

David

David White

I have now created the formula correctly, BUT have noted a new issue....

=LEFT([Bank Reconciliation Test Reference #]@row, 7) + LEFT([Accounts Receivable Test Reference #]@row, 7) + LEFT([Fixed Assets Test Reference #]@row, 7) + LEFT([General Ledger Test Reference #]@row, 7) + LEFT([Supply Chain - Inventory Test Reference #]@row, 7) + LEFT([Supply Chain - Purchasing Test Reference #]@row, 7) + LEFT([Supply Chain - System Admin Test Reference #]@row, 7) + LEFT([AT Statement Test Reference #]@row, 7) + LEFT([Prosthesis Test Reference #]@row, 7) + LEFT([Accounts Payable Test Reference #]@row, 7)

Works brilliantly, except for the fact that it adds a leading 0 to results when another column has data in, as per below:

So now need to get rid of this leading zero :(

Any ideas?

Nick Korna

@David White ,

No doubt there is a more elegant way to do this, but this formula should work:

=IF(ISBLANK([Bank Reconciliation Test Reference #]@row), "", LEFT([Bank Reconciliation Test Reference #]@row, 7)) + IF(ISBLANK([Accounts Receivable Test Reference #]@row), "", LEFT([Accounts Receivable Test Reference #]@row, 7)) + IF(ISBLANK([Fixed Assets Test Reference #]@row), "", LEFT([Fixed Assets Test Reference #]@row, 7)) + IF(ISBLANK([General Ledger Test Reference #]@row), "", LEFT([General Ledger Test Reference #]@row, 7)) + IF(ISBLANK([Supply Chain - Inventory Test Reference #]@row), "", LEFT([Supply Chain - Inventory Test Reference #]@row, 7)) + IF(ISBLANK([Supply Chain - Purchasing Test Reference #]@row), "", LEFT([Supply Chain - Purchasing Test Reference #]@row, 7)) + IF(ISBLANK([Supply Chain - System Admin Test Reference #]@row), "", LEFT([Supply Chain - System Admin Test Reference #]@row, 7)) + IF(ISBLANK([AT Statement Test Reference #]@row), "", LEFT([AT Statement Test Reference #]@row, 7)) + IF(ISBLANK([Prosthesis Test Reference #]@row), "", LEFT([Prosthesis Test Reference #]@row, 7)) + IF(ISBLANK([Accounts Payable Test Reference #]@row), "", (LEFT([Accounts Payable Test Reference #]@row, 7)))

I hope I have your column names correct; it not I hope you get the gist of what needs doing!

David White

Hi Nick,

It may be ugly, but the data is a thing of beauty! This is perfect!

Thank you for the help.

David