Display text between space X and X from cell.

Good morning all,

I'm hoping with all your combined expertise you will be able to help me. I am currently trying to build a formula that will pull text from a cell with between specific amount of spaces.

Example of what I need...

"00118385 B L S SL 10G 13 24 29.58 384.54 01/03/2011"
"00135641 * W C B M CN 133 24 36.21 1194.93 01/03/2011"

The above is the data entered cells. I require a formula that will pull out the number 13 from the middle of the first example (number 133 from the 2nd), by counting the number of spaces between the text.

The formula needs to look at the data within the cell from the right, as the number required is always between the 4th and 5th spaces from the right. The info on the left hand side however is variable, and the number of spaces can change from cell to cell. The length of all the information is also interchangable, so unfortunately I can't simply use "=right(len(..."

I have toyed with 'right(search(" ",' to no avail. I like to think I'm pretty useful with functions, but I think this one is beyond me.

Hopefully someone can help me!

Thanks (in advance!!),

Carl.

Thank you zbor, but I'm not sure where to enter this formula?

I have a chain of info like the example I gave down column A. Column B is empty but your formula refers to this cell? Obviously I can't paste it into B2 as it creates a circular reference. All I'm getting returned is #VALUE.

Does this formula contain functions not available in Excel 2003?

Unfortunately that only worked for 4 out of 22 of the cells. The other 18 I have all returned #VALUE.

I've attached a spreadsheet including your forumula, and also other data examples.

Thanks.

zbor you legend!!

You dear Sir, are a god!

Thank you very much.

zbor - couldn't agree more - this is amazing. What i'm trying to do is quite simple comparatively, but I can't extrapolate from what you created. I have a string of data in a cell that is three numbers, followed by a space, and then a varying number of words. I want to separate the numbers from the varying string of words. For example:

I have:
123 Department Black Cat
and I want:
"123" in one cell, and "Department Black Cat" in another

Another example is, I have:
456 Charming
and I want
"456" in one cell, and "Charming" in another.

do you have ideas / suggestions for this?

Thanks for the correction zbor! I've re-posted as a stand-alone.