How do I extract the last line from a cell?
Hello! I am trying to extract the last line from a cell and place it in a new column marked as "Current Status." Ideally, I want "This vendor is going to be retired by Nov 2022." in the Current Status column.
I can do this in excel using the below formula. any help is appreciated. thank you!
=TRIM(RIGHT(SUBSTITUTE(B5,CHAR(10),REPT(" ",200)),200))
Best Answer
-
Hey @Ahmed Iqbal
This will always find the last last line break of your cell and return the line, regardless of what text is written or how many lines you have.
=RIGHT([Privacy Team Use Only: DUPA Tracking Notes (i.e.,]@row, LEN([Privacy Team Use Only: DUPA Tracking Notes (i.e.,]@row) - FIND("~", SUBSTITUTE([Privacy Team Use Only: DUPA Tracking Notes (i.e.,]@row, CHAR(10), "~", LEN([Privacy Team Use Only: DUPA Tracking Notes (i.e.,]@row) - LEN(SUBSTITUTE([Privacy Team Use Only: DUPA Tracking Notes (i.e.,]@row, CHAR(10), "")))))
The "~" is there to give you an unusual placeholder. If this character could show up in your text, choose another unusual symbol
The LEN(your column)-LEN(Substitute([your column]...) calculates how many line breaks are in the string. Think of this as a COUNTIF function- and tells the substitute function which of the line breaks it should act on. Because it is giving you the total count, it is this term that always finds the last line break.
If the formula gives an unparseable then first double check my typing/spelling of your column name.
Does this work for you?
Kelly
Answers
-
Hey @Ahmed Iqbal
This will always find the last last line break of your cell and return the line, regardless of what text is written or how many lines you have.
=RIGHT([Privacy Team Use Only: DUPA Tracking Notes (i.e.,]@row, LEN([Privacy Team Use Only: DUPA Tracking Notes (i.e.,]@row) - FIND("~", SUBSTITUTE([Privacy Team Use Only: DUPA Tracking Notes (i.e.,]@row, CHAR(10), "~", LEN([Privacy Team Use Only: DUPA Tracking Notes (i.e.,]@row) - LEN(SUBSTITUTE([Privacy Team Use Only: DUPA Tracking Notes (i.e.,]@row, CHAR(10), "")))))
The "~" is there to give you an unusual placeholder. If this character could show up in your text, choose another unusual symbol
The LEN(your column)-LEN(Substitute([your column]...) calculates how many line breaks are in the string. Think of this as a COUNTIF function- and tells the substitute function which of the line breaks it should act on. Because it is giving you the total count, it is this term that always finds the last line break.
If the formula gives an unparseable then first double check my typing/spelling of your column name.
Does this work for you?
Kelly
-
Hi Kelly,
Thank you for the prompt response. This helped a lot!
Ahmed Iqbal
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!