Excel text formatting function interpreting 'E' in string as exponent

I have exported some data records from a database into spreadsheet form for final formatting. One of the key fields is text in the form of '123456E72'.

I have a formatting formula that formats the cell in the following way: =TEXT(cell,"000000000")

This causes excel to interpret it the cell as 123456 x 10^72. The original cell is already text, and I've also tried manually using 'Format Cells' as Text on the record cell, and I'm even using text formatting function and still getting a number? I'm using Excel in Office 365.

3 Answers

Assuming you want all of the values padded on the left with 0's to become 9 characters long, (and none of the entries are more than nine characters) you can try:

=IF(ISTEXT(C1),RIGHT("000000000"&C1,9),TEXT(C1,"000000000"))

It sounds you don't need the number-to-text conversion at all - why not simply:
=RIGHT("00000000"&A1,9)

To retain a text field exactly as is, when importing it into Excel or LibreOffice calc:

Find a way to prepend the text with a ' - it will make the spreadsheet to see the cell content as text, unconditionally.

Database export would imply using SQL, so do:
SELECT column1, "'" + column2, ...
... for prepending a ' to column2.

Excel and LibreOffice calc will NOT show this character, unless you edit the cell (e.g. hit F2 with the cell selected), nor consider it present when referencing the cell from other formulas.