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.

Q & A Collection

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