Salesforce and other SMB Solutions are coming soon. ×

# Extracting two sets of dimension from a text field ## Recommended Posts  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!

##### Share on other sites  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
)
)```

##### Share on other sites  Thanks. That's perfect.

##### Share on other sites  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?

##### Share on other sites  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 )
)```

##### Share on other sites  This is great. I should have asked for the field to be a number.

Thanks,

â€”John

• ### Images

• By Soliant Consulting,
• By Soliant Consulting,
• By Soliant Consulting,
• ### Forum Statistics

• Total Topics
33.7k
• Total Posts
141.5k
×
×
• Create New...