Expression Split and Conversion in Excel

 

Problem description

Below is book1.xlsx in an Excel file:

undefined

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.

 

Q & A Collection

https://stackoverflow.com/questions/63919652/is-it-possible-to-dynamically-convert-a-number-inside-a-cell-string