How to pull text after a multi-character "delimiter"?

PSExcellence
PSExcellence ✭✭
in Formulas and Functions

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)?

Tags:
· Share on FacebookShare on Twitter

Best Answers

Answers

  • AravindGP
    AravindGP ✭✭✭✭✭✭

    Hi @PSExcellence

    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

    · Share on FacebookShare on Twitter
  • PSExcellence
    PSExcellence ✭✭

    Hmm. For some reason all this formula is doing is removing the first two letters of the Company Name?

    · Share on FacebookShare on Twitter
  • Toufong Vang
    Toufong Vang ✭✭✭✭✭

    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!

    · Share on FacebookShare on Twitter
  • PSExcellence
    PSExcellence ✭✭
    Answer ✓
    https://community.smartsheet.com/discussion/comment/456462#Comment_456462

    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!

    · Share on FacebookShare on Twitter
  • BowenL
    BowenL ✭✭

    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.

    📅Book a meeting with me

    🤝 Purchase my expertise

    · Share on FacebookShare on Twitter
  • PSExcellence
    PSExcellence ✭✭

    Realizing I can't get any of these formulas to work, because some of the names contain "DBA" in all caps, and others contain "dba" lower-case.

    I need a formula that essentially says "If [Company Name]@row contains "dba" then return the portion of the name that falls AFTER dba, If [Company Name]@row contains "DBA" then return the portion of the name that falls AFTER DBA, and if neither of these are true then just return what's in [Company Name]@row"

    · Share on FacebookShare on Twitter
  • PSExcellence
    PSExcellence ✭✭
    Answer ✓

    FINALLY got it!! =IF(FIND("dba", [Company Name]@row) > 0, MID([Company Name]@row, FIND("dba", [Company Name]@row) + 4, LEN([Company Name]@row)), IF(FIND("DBA", [Company Name]@row) > 0, MID([Company Name]@row, FIND("DBA", [Company Name]@row) + 4, LEN([Company Name]@row)), [Company Name]@row))

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!