Expression Split and Conversion in Excel
Problem description
Below is book1.xlsx in an Excel file:
Cell A1 contains size data represented by inches. We are trying to convert it to sizes whose units are centimeter and enter the result to cell B1.
The task covers a series of operations, including getting substrings from a string, string split, and conversion of sequence to string.
Directions
1. Load Excel add-in ExcelRaq.xll.
2. Type in the following formula in Excel B1:
=esproc("=left(?,-1).split@t(""x"").(string(number(~)*2.54,""#.00"")).concat(""x"")+""cm""",A1)
esproc function calls the add-in to compute the expression. left(?,-1) means removing the last character from A1’s value; split function splits it as a sequence by x; @t option enables deleting white spaces from both ends of each member. Then loop each member of the sequence to convert it to a number that is multiplied by 2.54 and the result is formatted to retain two decimal places. Finally, the result members are concatenated by "x" into a string ended by cm.