EXTRACT WHOLE NUM and FRACTIONS: into different columns

[itr674]

Need a formula to extract length x width x height from a single column into three columns.
Sample data:
31/2" x 25/8" x 11/4 extract into separate columns as 3 1/2" 2 5/8" 1 1/4"
131/2" x 8" x 63/4" extract into separate columns as 13 1/2" 8" 6 3/4"

The max length of whole number is two (two digits/characters.
Dropping this in from another program but want to separate for netter filterability.

 

[AlanY]

have you tried the built-in text to columns function (under the data menu) using x as the delimited?

 

[mohadin]

Hi
Any way With formulas
If your data in column A2

B2=TRIM(MID(SUBSTITUTE(A2,"x",REPT(" ",99)),1,99))
C2=TRIM(MID(SUBSTITUTE(A2,"x",REPT(" ",99)),99,99))
D3=TRIM(MID(SUBSTITUTE(A2,"x",REPT(" ",99)),2*99,99))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also do you need the spaces adding between the whole number & the fraction, or are they already there?

 

[Peter_SSs]

Could you have a measurement like 111/16"?
If so, how would we know if it was 1 11/16" or 11 1/16"?

 

[itr674]

The fact there is no space was what was messing me up, AlanY's substitute formula worked, so far...

AlanY, thanks for reply, and I did try text-to-columns but to figure out formatting.

mohadin, thanks for reply, and formulas worked great, trying to add space now...

Fluff, thanks for reply, and have updated my account, and a space, or "-", would be good.

Peter_SSs, thanks for reply, and right now I don't see any 111/16, but that could be a problem...

 

[Peter_SSs]

If those first two suggestions work for you that is great.
From your thread title I thought that you wanted the whole numbers in their own columns and the fractions in their own columns like this. ?

22 03 21.xlsm
	A	B	C	D	E	F	G
2	31/2" x 25/8" x 11/4	3	1/2	2	5/8	1	1/4
Sheet2 (5)

In future, you could help clarify that sort of thing by using XL2BB

 

[itr674]

If those first two suggestions work for you that is great.
From your thread title I thought that you wanted the whole numbers in their own columns and the fractions in their own columns like this. ?

In future, you could help clarify that sort of thing by using XL2BB

Copy, yep my comm was bad, and will use XL2BB next time, if I can remember, I old...

 

[itr674]

Could still use spaces...

3 1⁄2"	2 5⁄8"	1 1⁄4"
3 1⁄2"	2 5⁄8"	2"

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D
1
2	31/2" x 25/8" x 11/4"	3 1/2"	2 5/8"	1 1/4"
3	131/2" x 8" x 63/4"	13 1/2"	8"	6 3/4"
Main

Cell Formulas
Range	Formula
B2:D3	B2	=IFERROR(REPLACE(TRIM(MID(SUBSTITUTE($A2,"x",REPT(" ",100)),COLUMNS($B2:B2)*100-99,100)),LEN(TRIM(MID(SUBSTITUTE($A2,"x",REPT(" ",100)),COLUMNS($B2:B2)*100-99,100)))-3,0," "),TRIM(MID(SUBSTITUTE($A2,"x",REPT(" ",100)),COLUMNS($B2:B2)*100-99,100)))