How to remove last character in a concatenation

Julie Caliri

I have a series of columns concatenated, separated by _ (so it looks like "A_B_C_D_E_...")

Some all columns after E are optional.

The end result means some concatenations end in "_"

I think a helper column might be needed, in which case the formula would be something like that solves for 'if row ends in "_", remove "_" - but I can't seem to get it to work.

Anyone know how to solve this seemingly simple need? Thank you!

ker9

Is this what you need?

=IF(RIGHT([Column6]@row, 1) = "_", LEFT([Column6]@row, LEN([Column6]@row) - 1), [Column6]@row)

markkrebs

Could use a helper with the formula =LEFT([Column6]@row, LEN([Column6]@row) - 1)

This would take off that last character

Julie Caliri

Mark - that does work (thank you!), however not all the way solved.

That doesn't work when the last character is NOT an underscore. So I need to add an IF part to this.

ker9

Is this what you need?

=IF(RIGHT([Column6]@row, 1) = "_", LEFT([Column6]@row, LEN([Column6]@row) - 1), [Column6]@row)

Julie Caliri

@ker9 This works! I don't quite know why, but it does :)

thank you!

ker9

@Julie Caliri

The first part is looking to see if the last character is an underscore:

=IF(RIGHT([Column6]@row, 1) = "_",

If it is an underscore (True), then return text (from left) minus last character (Length-1):

LEFT([Column6]@row, LEN([Column6]@row) - 1),

If not an underscore (False), then return what is in column6:

[Column6]@row)