excelexcel-formulaexcel-2019

Extract text after last delimiter but exclude last character

I'm needing to extract a couple of pieces of text from a text field that sometimes contains multiple of the same delimiter. I need characters 3-6 and then the text from the end, but I also don't want the last character.

Example:

1-7483742 (first part of the string-second part of the string)
1-1234742 (first part of the string-some other part)
1-5678742 (first part of the string-and more text)

Results needed:

7483 second part of the string
1234 some other part
5678 and some more text

Among other things, I have tried this:

=IFERROR(RIGHT(LEFT([@[sourcecolumn]],6),4)&" "&
MID([@[sourcecolumn]], FIND("-", [@[sourcecolumn]], FIND("-", [@[sourcecolumn]])+1)+1,256),
RIGHT(LEFT([@[sourcecolumn]],6),4))

This gets me the closest but it keeps the last character.

Solution

  • This works on my end:

    enter image description here

    =LET(
     α, CHOOSECOLS(TEXTSPLIT(A1,{"-","(",")"},,1),2,4), 
     LEFT(TAKE(α,,1),4)&" "&TAKE(α,,-1))

    • For Older Versions could use the following:

    =MID(A1,3,4)&" "&TRIM(RIGHT(SUBSTITUTE(LEFT(A1,LEN(A1)-1),"-",REPT(" ",100)),100))