Updating a MID formula

[amxtomzo]

Hello and thanks for helping,
We copy data from a PDF file into a spreadsheet and a formula will return 1 of the columns of data that we need.
Below is the working formula and the info we get our data from.
The data gets pasted into cell A8, the formula is in cell G8 then copied down column G for several rows.

this is what was previously pasted into cell A8:

*001 RJ 25 25 1,426 226 60 25 0 0 0 0 0 0

This is the formula we were using to get the 5th column of data:
it would return "1,426" in this case:

=MID(MID(MID(SUBSTITUTE(A8," ","^",4),1,256),FIND("^",SUBSTITUTE(A8," ","^",4)),256),2,FIND(" ",MID(MID(SUBSTITUTE(A8," ","^",4),1,256),FIND("^",SUBSTITUTE(A8," ","^",4)),256))-2)

Now the data we are pasteing is looking like this:

*001      RJ           25      25   1,262   1,162         1       25       0        0         0       0            0      0

The formula will not return any data, it shows this: "#value!
in this case we would like it to return 1,262, still the 5th column of data,
it is now just spaced out much further apart.

Any help in getting the 5th column of data would be most appreciated,
I am not against a new formula either

thank you so much

Thomas

 

[Misca]

Try something like:
=MID(SUBSTITUTE(A2," ",""),11,5)

The SUBSTITUTE replaces all the spaces with nothing rather than special characters. This way the formula should work as long as the previous digits have a fixed length.

If not, You just have to wrap the original SUBSTITUTE formula with several other substitute formulas, where a double special character is replaced with a single one. Not the prettiest solution but should work as long as you have enough substitutes to knock out the very last double special character.

 

[amxtomzo]

Misca
thanks for looking, Sadly the digits do not have a fixed length, they can be different row to row.
example, these are the first 4 rows of data of what could be pasted into cells A8 and below:

Wild idea, that i do know how to resolve, MAYBE??
is is possible to make the data 1 space apart within a cell?

just guessing at ideas,

-----     -------   -----  ------- ------- -------  --------  -------------- -------- --------- ------- ------------  -----
*001      RJ           25      25   1,262   1,162         1       25       0        0         0       0            0      0
*003      RJ           16      16     690     690         0       16       0        0         0       0            0      0
*012      RJ            1       1     100     100         0        1       0        0         0       0            0      0
*005      RJ            7       7     387     387         0        7       0        0         0       0            0      0

Thomas

 

[Fluff]

How about

+Fluff New.xlsm
	A	B
1
2	*001 RJ 25 25 1,262 1,162 1 25 0 0 0 0 0 0	1262
3	*003 RJ 16 16 690 690 0 16 0 0 0 0 0 0	690
4	*012 RJ 1 1 100 100 0 1 0 0 0 0 0 0	100
5	*005 RJ 7 7 387 387 0 7 0 0 0 0 0 0	387
Results

Cell Formulas
Range	Formula
B2:B5	B2	=TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",100)),400,100))+0

 

[amxtomzo]

WOW,
Big Thank you, worked perfectly,
copied down several rows, all is good again,

Would it be possible to explain what is happening in the formula?
i would like learn what it is and or , how it is doing it.

thanks again

Thomas

 

[Fluff]

Initially it removes all the extra spaces
TRIM(A2)
then it replaces the remaining spaces with 100 spaces each
SUBSTITUTE(TRIM(A2)," ",REPT(" ",100))
then it extracts 100 characters from the data starting at the 400th character & trims it to remove the extra spaces & finally adds 0 which will convert it from text to a number.

HTH

 

[amxtomzo]

HTH
thank you so much for the info,
its always nice to try and learn a little something

thank you again
have a great day

Thomas

 

[Fluff]

Glad to help & thanks for the feedback.