Cutting Text after third space and after fourth

[will31]

Hi,

I have an output from a database thats looks something like;

3 55 XXX0001 222333 A P1 day 5 13 hour

I need to cut out the 222333 part which I tried with MID but the first digit (3 in the example) can be >10 in the same data set.

Any ideas how to cut after the 3rd blank space and after the 4th to get the ID?

Thanks In Advance

Will

 

[delaneyjm]

If your string is in A1, perhaps:

=MID(A1,SEARCH(CHAR(127),SUBSTITUTE(A1," ",CHAR(127),3))+1,SEARCH(CHAR(127),SUBSTITUTE(A1," ",CHAR(127),4))-(SEARCH(CHAR(127),SUBSTITUTE(A1," ",CHAR(127),3))+1))

 

[mikerickson]

Try
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, " ", "", 1), " ", "", 1), " ", REPT(" ",255)), 255, 255))

 

[will31]

Works a dream

thanks both

Will