Convert dimensions in feet and inches of a three dimensional object into separate columns

[diegodad]

I need to convert text describing the dimensions, in feet and inches, of a three dimensional object into numbers in separate columns. Here's an example:
Convert 1'2" x 30'40" x 50'6" (all in one cell) into six separate columns containing 1 2 30 40 50 6

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H
1	1'2 X 30'40 x 50'6		1	2	30	40	50	6
Sheet21

Cell Formulas
Range	Formula
C1:H1	C1	=MID(SUBSTITUTE(SUBSTITUTE(LOWER(A1),"x","'"),"'",REPT(" ",99)),SEQUENCE(,6,1,99),99)+0
Dynamic array formulas.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Convert dimensions in feet and inches of a three dimensional object into separate columns
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[jtakw]

Hi,

Based on your 1 ( Single ) sample:

Book3.xlsx
	A	B	C	D	E	F
1	1'2" x 30'40" x 50'6"	1	2	30	40	50
Sheet984

Cell Formulas
Range	Formula
B1:F1	B1	=MID(SUBSTITUTE(SUBSTITUTE($A1,""" x","'"),"'",REPT(" ",100)),COLUMNS($B1:B1)*100-99,100)+0