How do I extract 2 characters from the right after the nth space in a cell

[rangeralex]

I am trying to create a conversion tool for data I get in a PDF. I have a rows worth of data that all shows up in the same cell when pasted into Excel. I am trying to use formulas to separate the data to make the process repeatable in an easy fashion. (Paste into sheet 1, auto converted copy appears like magic on sheet 2).

I used this formula in another portion of the workbook already for a similar task; however it wont work in this section due to a variable number of spaces in the middle of each cell. However, it will work if I can figure out a way to do it starting from the right side of the data in the target cell.

(Formula that worked in another section)
=MID(A1,FIND("*",SUBSTITUTE(A1," ","*",1))+1,2)

I have tried something similar to this:
=RIGHT(A1,FIND("*",SUBSTITUTE(A1," ","*",1))+1)
however, this is not counting the spaces in from the right side correctly.

Advice would be appreciated.

 

[Rick Rothstein]

Assuming the value in cell A1 contains less than 300 characters, give this formula a try...

=RIGHT(TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",300)),3000),300)),2)

 

[rangeralex]

Thanks for the reply, but I'm not sure I understand how the formula works. What part of the formula tells excel how many spaces to skip and what part indicates the number of characters that should be displayed after that space is found?

To clarify if the cell contained this text: SKU 12345 Red Blue Green 9 89 28 59 3 12/31/2018

I want the formula to count starting at the date the number of spaces and return the number 9 after 5 spaces. I will repeat the formula for all numbers ie: 89, 28, 59, 3. It should work with the TRIM function if is set it to a 2 character limit.

 

[TheNewUnion]

Is the string of text always the same length? Or always in the same format perhaps. I was thinking along the lines of finding the last space as a starting point, so something like this
=FIND("^^",SUBSTITUTE(A2," ","^^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))

That way we have the final space, if it was always e.g. SKU 12345 Red Blue Green then we could find the 5th Space and chop out everything between the two spaces, the just use Right or Mid to separate everything out into individual cells.
Alternatively could just Text to Columns using a space as the delimiter

 

[bosco_yip]

To extract value starting at the date and return the number 9 after 5 spaces.

1] In A1, enter: SKU 12345 Red Blue Green 9 89 28 59 3 12/31/2018

2] In A2, enter: SKU 9876 9 25 68 12 54 12/21/2019 98 SKU 9876

3] In B1, formula copied down :

=TRIM(LEFT(RIGHT(SUBSTITUTE(LEFT(A1,FIND("/",A1)-1)," ",REPT(" ",99)),599),99))

Regards
Bosco