microsoft excel – Convert column to comma-separated string, maintaining scientific notation


I have seen many people online ask about converting a column formatted list to a comma-separated list in the form of a string. I understand how to do that part, but I want the values I’m copying to the new string to maintain their scientific notation format.

A1 | 1.2E+00
A2 | 2.3E-03
A3 | 3.4E+04

I use the following to produce cells with the number and a following comma. Then those cells are transposed.

B1=A1&","

The problem is when B1 is defined, the values are reformatted to the “general” type. I want my transposed columns to look like this…

1.2E+00, | 2.3E-03 | 3.4E+04

But it’s coming out like this instead

1.2, | 0.0023, | 34000,