How to remove last character in a concatenation

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!

Find more posts tagged with

Formulas

Accepted answers

ker9

Is this what you need?

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

All comments

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)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.