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.



1. Load Excel add-in ExcelRaq.xll.

2. Type in the following formula in Excel B1:


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.


Q & A Collection