How to pull text after a multi-character "delimiter"?
Tried searching and couldn't find another discussion that matched my issue. I am attempting to write a formula to extract only what falls after the "DBA" portion of a company's name. For example if the company name is "Vance Refrigeration DBA Kool Guyz" then I just want to pull "Kool Guyz". I've tried using FIND and LEFT, but using "dba" as a delimeter doesn't work (because it can apparently only be one character)?
Best Answer
-
Modified it slightly to account for company names that do not contain DBA, and ended up with =IF(CONTAINS("dba", [Company Name]@row), MID([Company Name]@row, FIND("DBA", [Company Name]@row) + 4, 200), [Company Name]@row)
Thanks so much!
Answers
-
You can try this
=RIGHT([Company Name]@row, LEN([Company Name]@row) - 2 - FIND("DBA", [Company Name]@row))
Thanks,
Aravind GP| Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
-
Hmm. For some reason all this formula is doing is removing the first two letters of the Company Name?
-
Hi, @PSExcellence , another approach is to use MID().
MID([Company Name]@row, FIND("DBA",[Company Name]@row) + 4, 200)
FIND() "DBA"; then return the 200 characters that are 4 characters from the position where "DBA" is found.
If you want to be precise, use
LEN([Company Name]@row) - FIND("DBA",[Company Name]@row) - 4
instead of "200". (But "200" requires fewer keystrokes!🙂)Cheers!
-
Modified it slightly to account for company names that do not contain DBA, and ended up with =IF(CONTAINS("dba", [Company Name]@row), MID([Company Name]@row, FIND("DBA", [Company Name]@row) + 4, 200), [Company Name]@row)
Thanks so much!
-
Hi @PSExcellence
here you go.=MID([Column2]@row, FIND("DBA", [Column2]@row) + 3, LEN([Column2]@row) - FIND("DBA", [Column2]@row) - 2)
Bowen Liu | Smartsheet Expert
If you would like professional support, please consider using my service below.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!