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

Tags:

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

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

  • 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!

  • PSExcellence
    PSExcellence ✭✭
    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!

  • 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

  • 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"

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!