Convert table from excel to latex with format $10^{-n}$

Question

I would like to convert a table from Mircosoft Excel to latex (on Winedt) based on Microsoft Windows operation system. The number in excel is, for instance,

---

1 2

1.00E-02 1.05E-03

---

I would like to have 1.00 $\times 10^{-2}$ and 1.05$\times 10^{-3}$ in latex table directly.

I have tried excel2latex.xla, it could convert table, but does not change formatting as $\times 10^{-2}$. Is there any simple way to do that? though I am somehow doubt about it...

Answer 1

The package siunitx could be a solution for you, but it requires some adjustment of the excel2latex output, which I'll explain below. siunitx defines a special column type, S used in place of the usual l,c,r specifications.

Each cell entry is then taken as the argument to siunitx's \num{} command, which accepts scientific notation input as 1.00E-02.

One behavior of this solution is that siunitx centers the columns around the decimal separator. siunitx tries to be smart about what is text/headings and what is numerical data. But anything that could be mistaken for numerical data (in your example, the first row, which I'm assuming contains headings) should be protected with curly braces.

So, to apply this solution to excel2latex output, you'll have to:

1. load the siunitx package in the document preamble,
2. change column specifiers from c (or l or r) to s in the beginning of the tabular environment for any columns with numerical data, and
3. protect any non-numerical data that shouldn't be aligned at the decimal separator with curly braces {}.

An example:

Before:

\begin{tabular}{cc}
1 & 2 \\
1.00E-02 & 1.05E-03 \\
\end{tabular}

After:

\begin{tabular}{SS}
{1} & {2} \\
1.00E-02 & 1.05E-03 \\
\end{tabular}

Complete Code and Output:

\documentclass{article}
\usepackage{siunitx}

\begin{document}
\begin{tabular}{SS}
{1} & {2} \\
1.00E-02 & 1.05E-03 \\
\end{tabular}
\end{document}

Many adjustments can be made for alignment and spacing of the S columns. If you decide this solution is workable for you, you can consult the package manual (Section 4.6) for details.

Answer 2

The literal answer likely requires a bit of formula writing in Excel. Given a numeric value 1.05E-3 in cell B2, you can extract the mantissa and exponent with the following formulas (via getting the exponent and mantissa from scientific format at Mr. Excel):

• Exponent (cell F2): =INT(LOG(B2))
• Mantissa (cell E2): =B2/10^F2
• Formatted text version (cell I2): ="$"&TEXT(E2,"0.00")&"\times 10^{"&F2&"}$"

But here’s a much simpler alternative answer using the siunitx package and its S column type (section 4.6):

\documentclass{article}
\usepackage{siunitx}
\begin{document}
\begin{tabular}{SS}
1.00E-02 & 1.05E-03
\end{tabular}
\end{document}