Extracting two sets of dimension from a text field

[jdewolf@corcoran.org]

I have inherited a database that contains various information including a single dimensions field. The dimensions are only useful to me if I can look both height and width as unique fields. Can you suggest an easy way to extract the dimensions and populate two fields instead. Typical entries are:

 

42 3/8 x 33 11/16 inches

20 x 16 inches

19 3/4 x 16 inches

14.5 x 11.5 inches

 

In general, the "inches" work is consistent, as is the " x ". Any advice would be helpful. Thanks in advance!

[David Head]

Try these calculations:

 

firstDimension = 
Trim( Left( dimensions; Position( dimensions; "x"; 1; 1 ) - 1 ) )

secondDimension = 
Trim(
 Middle(
   dimensions;
   Position( dimensions; "x"; 1; 1 ) + 1;
   Position( dimensions; "inch"; 1; 1 ) - Position( dimensions; "x"; 1; 1 ) - 1
 )
)

[jdewolf@corcoran.org]

Thanks. That's perfect.

[jdewolf@corcoran.org]

Thanks again.

 

If you don;t mind, how do I ensure the new field is a number so that the fractions are convereted to decimals?

[David Head]

How did I know you were going to ask that? smiley-wink

 

Try these modified calculations:

 

Dimension1 =

Let ([
field = dimensions;
posx = Position ( field ; "x" ; 1 ; 1 );
firstnum = Trim ( Left ( field; posx - 1 ) );
expression = Substitute ( firstnum ; " " ; "+" )
];
Evaluate ( expression )
)

 

Dimension2 =

Let ([
field = dimensions;
pos1 = Position ( field ; "x" ; 1 ; 1 );
pos2 = Position ( dimensions ; "inch" ; 1 ; 1 );
secondnum = Trim ( Middle ( field ; pos1 + 1 ; pos2 - pos1 - 1 ) );
expression = Substitute ( secondnum ; " " ; "+" )
];
Evaluate ( expression )
)

[jdewolf@corcoran.org]

This is great. I should have asked for the field to be a number.

Thanks,

—John