Extract Space and ft

Here's one that works.
I am sure there is one that's shorter.
I'll see if I can work it out later.

HTML Code: 

Try this

Formula:

Please Login or Register  to view this content.

If you want to extract values in Col A into separate cells use this:
Enter formula in B2 and drag it across and down

Formula:

Please Login or Register  to view this content.

v	A	B	C	D
1	Dimensions	Left	Mid	Right
2	25.3 X 8.11 X 9.5 ft	25.3	8.11	9.5
3	24.3 X 8.11 X 9.2 ft	24.3	8.11	9.2
4	28.1 X 8.11 X 10.1 ft	28.1	8.11	10.1
5	29.8 X 11.1 X 9.1 ft	29.8	11.1	9.1
6	29.6 X 8.4 X 10.6 ft	29.6	8.4	10.6
7	31.2 X 10.5 X 9.7 ft	31.2	10.5	9.7

Hi MnMCarta,

Here is another method I think you should learn. It uses the TRIM function that removes all spaces before and after any text or numbers.

=TRIM(SUBSTITUTE(MID(SUBSTITUTE($A12,"X",REPT(" ",30)),30*(COLUMNS($A:A)-1)+1,30),"ft",""))

Blow Up Spaces Mid and Trim.xlsx

Here is how it works:
Start with your
25.3 X 8.11 X 9.5 ft
If you replace each of the "X" with 30 spaces (using "Substitute($a12,"X",Rept(" ",30))") it will look like:
25.3 8.11 9.5 ft
Then you need to grab the first 30 characters and TRIM() them and the next 30 characters and TRIM() them and then the last 30 characters and Trim() them.
This TRIM() will remove all those spaces you just put into the string but leave the non space characters.
Just before you do that, you need to Substitute that pesky "ft" with nothing, using Substitute(...,"ft","").

The Mid() function allow you to start at a different part of the string and you want to start at 1,30 and 60 and take the next 30 characters before trimming them.

I hope this formula is a little cleaner and is a tool you can use for many of your problems.

It looks like AlKey beat me to the formula. Learn it instead of the "~" formula as it is a little more versatile.

....what the heck the "~" part means...

In the formula:

=RIGHT(A2,LEN(A2)-FIND("~",SUBSTITUTE(A2,"X","~",2)))

The SUBSTITUTE function replaces the second X in the dimensions string with the TILDE (~) character, resulting in 25.3 X 8.11 ~ 9.5 ft
The FIND function returns the position of tilde, which indicates the starting position of the last dimension.
The number of characters returned is calculated with:

LEN(A2)-FIND("~",SUBSTITUTE(A2,"X","~",2))

which includes the " ft"