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!
Best Answer
-
Is this what you need?
=IF(RIGHT([Column6]@row, 1) = "_", LEFT([Column6]@row, LEN([Column6]@row) - 1), [Column6]@row)
Answers
-
Could use a helper with the formula =LEFT([Column6]@row, LEN([Column6]@row) - 1)
This would take off that last character
-
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.
-
Is this what you need?
=IF(RIGHT([Column6]@row, 1) = "_", LEFT([Column6]@row, LEN([Column6]@row) - 1), [Column6]@row)
-
@ker9 This works! I don't quite know why, but it does :)
thank you!
-
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)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!