Extracting Specific Text within a variable text string

[Chris8630]

Hi all
I'm working on a way to automate a invoice checking which my colleague was doing manually. We get a PDF copy of the invoice which I now convert to Excel via Adobe. Unfortunately the output looks horrific so I'm trying to go through and extra certain bits of information, some I'm fine with however there are some I have no idea how to extra out, made worse by the variability of the cell contents. So for example I have a cell which contains the following text sting (data references changed for security but I've left the number of characters the same)

AAAAA 150 BBB 150 BBB CCCCC-DDD

So the AAAAA is not needed data, the first 150 BBB is the ordered amount and the second 150 BBB is the delivered amount. Some of the cells then end of with a product code (CCCCC-DDD in this case) but not always. I'm after the underlined number and I don't need any of the rest of the stuff in the cell. As the invoices can cover 100's of products manually extracting the data doesn't really fix my issue. Is there any way I can extract this specific bit of text from the middle of all of this please?
Thank you all in advance for your help.

 

[Fluff]

Will it always be the 2nd number in the cell?

 

[Chris8630]

Will it always be the 2nd number in the cell?

It should be, the first bit of the cell is normally a mixture of letters and numbers. The BBB's are letters and the part number at the end is normally a mixture of letters and numbers

 

[Fluff]

Ok, how about

+Fluff 1.xlsm
	A	B
1
2	AAAAA 150 BBB 155 BBB CCCCC-DDD	155
Lists

Cell Formulas
Range	Formula
B2	B2	=FILTERXML("<k><m>"&SUBSTITUTE(A2," ","</m><m>")&"</m></k>","//m[.=number()][2]")

 

[JEC]

Or with regex

Function jec(cell As String)
 With CreateObject("VBscript.RegExp")
   .Global = True
   .Pattern = "\d{1,10}"
   jec = .Execute(cell)(1)
 End With
End Function

=jec(A1)

 

[Fluff]

Another option if the number you want is always between the 3rd & 4th space

=REPLACE(LEFT(A2,FIND("^",SUBSTITUTE(A2," ","^",4))-1),1,FIND("^",SUBSTITUTE(A2," ","^",3)),"")+0

 

[Sufiyan97]

Another option

=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",199)),599,99))+0

 

[Chris8630]

Ok, how about

+Fluff 1.xlsm
	A	B
1
2	AAAAA 150 BBB 155 BBB CCCCC-DDD	155
Lists

Cell Formulas
Range	Formula
B2	B2	=FILTERXML("<k><m>"&SUBSTITUTE(A2," ","</m><m>")&"</m></k>","//m[.=number()][2]")

Hi Fluff, thanks for this, one the first example I've got in my file this works spot on. I'll run it through the rest of the file and check it grabs the info on the rest. Really appreciate the help

 

[Fluff]

Glad we could help & thanks for the feedback.