0

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.

Improve this question
4
  • What do you want to return? Text will try to convert text to a number. and are you sure the cell is text and not a scientific notation number?
    – Scott Craner
    Commented Feb 19, 2021 at 18:15
  • I'd like it to just return 123456E72. I thought the purpose of Text() is to format numbers as text? The intent of the formula in this workbook is to ensure that the records (some of which are numerical and may not be 9 digits) end up as 9 digit text records. The original record cell is definitely text and not in scientific notation.
    – dwcecil
    Commented Feb 19, 2021 at 18:23
  • Then try this: IF(ISNUMBER(cell),TEXT(cell,"000000000"),cell) TEXT will try to convert text to a number to apply the format. So =TEXT("123","0000") will return 0123 even though the input is text. Excel sees #E# as scientific notation and will try to convert it.
    – Scott Craner
    Commented Feb 19, 2021 at 18:26
  • I had the same problem, but I deleted the columns, formatted the columns as text (right click > format cells) and then pasted my data. It pasted correctly and added those little annoying green nags in the corner of each of the cells it would otherwise have mangled, with the message "The number in this cell is formatted as text or preceded by an apostrophe"
    – sahmeepee
    Commented Aug 25, 2023 at 9:21

3 Answers 3

Reset to default
0

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"))

enter image description here

Improve this answer
0

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

Improve this answer
0

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.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .