Need help extracting a substring

[126driver]

I don't know why I can't figure this out. I have the following text string in cell A2:

162780 162780 DECAL PUMP 5X4 7/16 DCL082B EA 750 750

I need to extract the last "750" and second to last "750" to separate cells. There is always a "EA" within the string before these counts (which are quantity ordered and quantity shipped). I have the formula: =MID($A2,SEARCH("EA",$A2)+3,3) to extract the "750" after "EA" right now, but the issue is that these counts are not always three digits, could be 1 - 4, always separated by a space. I know I need to insert additional formula instead of "+3" probably using SUBSTITUTE but I've searched and have hit a roadblock. Any help is tremendously appreciated and if anyone can point me to a good resource to learn more about LEFT, RIGHT, SUBSTITUTE, MID and LEN that would also be appreciated. Any suggested courses on the subject?

 

[alansidman]

Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByEachDelimiter({"EA "}, QuoteStyle.Csv, true), {"Column1.1", "Column1.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column1.2", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.2.1", "Column1.2.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Column1.1"})
in
    #"Removed Columns"

Book2
	A	B	C	D
2	Column1		Column1.2.1	Column1.2.2
3	162780 162780 DECAL PUMP 5X4 7/16 DCL082B EA 750 750		750	750
Sheet1

 

[Anthony47]

Another option, using formula: in A2

=LET(aSPLIT,TEXTSPLIT(A2,"EA "),bSplit,TEXTSPLIT(INDEX(aSPLIT,1,COLUMNS(aSPLIT))," "),--bSplit)

Cartel1
	A	B	C
1
2	162780 162780 DECAL PUMP 5X4 7/16 DCL082B EA 750 750	750	750
3
Foglio1

Cell Formulas
Range	Formula
B2:C2	B2	=LET(aSPLIT,TEXTSPLIT(A2,"EA "),bSplit,TEXTSPLIT(INDEX(aSPLIT,1,COLUMNS(aSPLIT))," "),--bSplit)
Dynamic array formulas.

 

[kweaver]

Are there always 10 "fields" ? If so, how about:

=INDEX(TEXTSPLIT(A1," "),,{9,10})

 

[kweaver]

Alternatively, if there are more or fewer "fields", see if this does it:

=INDEX(TEXTSPLIT(A1," "),,COUNTA(TEXTSPLIT(A1," "))-{1,0})

 

[126driver]

Thanks for all of the help! I'm unknowledgeable in Power Query - need to check that out. Other options worked perfectly. @kweaver, I'm not sure if the fields will vary, both of your solutions worked too. Thanks!

 

[alansidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[Dave Patton]

TextBefore.xlsm
	A	B	C	D
1
2	162780 162780 DECAL PUMP 5X4 7/16 DCL082B EA 750 750		750	750
3	162780 162780 DECAL PUMP 5X4 7/16 DCL082B EA 7502 7502	7502	7502
4
2b

Cell Formulas
Range	Formula
B2:D2	B2	=TEXTSPLIT(TEXTAFTER(A2,"EA")," ")
B3	B3	=TEXTBEFORE(TEXTAFTER(A3,"EA ")," ")
C3	C3	=TEXTAFTER(TEXTAFTER(A3,"EA ")," ")
Dynamic array formulas.