Forum Discussion

jklemm1's avatar
jklemm1
Copper Contributor
Oct 17, 2022

Extraction formula help

I need a formula to extract the AC-UG from the text string below and then put them in two separate columns without using the text-to-columns feature - TIA!    EM-AC-UG-Course-ENG-122-Week3-TOT
excel
Formulas and Functions
Nothing_Left_to_Lose's avatar
Nothing_Left_to_Lose
Brass Contributor
Oct 17, 2022

jklemm1 

 

The custom function AnyPart can do that.
It is part of the free "Custom_Functions" Excel add-in.

(20+ excel functions that work exactly like the built-in functions)

 

(filled across and then filled down)

Pay attention to the placement of the $ symbols.

 

Download the add-in from OneDrive...

https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU

A separate examples file is also available.

 

 

'---

Nothing Left to Lose

  • mathetes's avatar
    mathetes
    Silver Contributor
    Oct 17, 2022

    jklemm1 

     

    I have used the LET function in the attached. So you will need the newer version of Excel.

     

    Other than that, the two formulas use the most basic of Text functions, FIND, LEFT, MID and RIGHT.  I've attached a spreadsheet with your extractions done.

    This formula that extracts the second and third sets of characters is

    =LET(

    strt,FIND("-",A1)+1,

    midl,FIND("-",A1,strt),

    fnl,FIND("-",A1,midl+1),

    MID(A1,strt,fnl-strt)

    )

    No doubt there are more elegant ways to do this, but I assume you'll be able to follow that.

     

    And this one concatenates the remainder into a single next, leaving out the extracted text.

    =LET(

    hypa,FIND("-",A4),

    hypb,FIND("-",A4,hypa+1),

    hypc,FIND("-",A4,hypb+1),

    LEFT(A4,FIND("-",A4))&RIGHT(A4,LEN(A4)-hypc)

    )

     

     

Resources