Grabbing characters when there are multiple transition characters.

Rootools
Rootools ✭✭
edited 12/12/23 in Smartsheet Basics

I have had a lot of success utilizing the resources gathered here, but I need help with this one. The piece here is already an extraction of a larger string of characters. But, how would a formula be written to extract the number "92630" from this string with multiple dashes?: "pms-87-92630-05". I have managed to collect the other groups but am stumped having only amateur experience with formulas. Please help. :) Edit - I'm actually really close right now using =RIGHT(LEFT([Full Nom]@row, LEN([Full Nom]@row) - VALUE(FIND("-", [Full Nom]@row))), FIND("-", LEFT([Full Nom]@row, LEN([Full Nom]@row) - VALUE(FIND("-", [Full Nom]@row))))), but receiving only 9263. The zero doesn't seem to be a factor after switching numbers.

Answers

  • Leibel S
    Leibel S ✭✭✭✭✭✭

    @Rootools

    Below should work.

    =IFERROR(MID([Full Nom]@row + "-", FIND("~", SUBSTITUTE([Full Nom]@row + "-", "-", "~", 2)) + 1, FIND("~", SUBSTITUTE([Full Nom]@row + "-", "-", "~", 3)) - FIND("~", SUBSTITUTE([Full Nom]@row + "-", "-", "~", 2)) - 1), " ")

    Change the bolded numbers if you want to pull a different value (from this - separated field)

    increase the number to get the 4th value and decrease the numbers to get the 2nd value etc