Text (number) , then csv save changes it back to number

**7thMonth** · 06-12-2017, 01:51 PM

I have a worksheet where I use a Vlookup and IF functions, In the if functions the results are numbers, but in a text form. IN other words the result is coming from a text cell with "123456789000" in it. When I copy the worksheet to a new worksheet everything gets transferred OK. I then save the new worksheet as a csv (ms-dos) file. When I open the CSV file instead of "123456789000' I get "1.2E+11. How do get it so save the full number, which is really a text.

This is nit a column width issue. but some kind of conversion issue.

**oeldere** · 06-12-2017, 02:05 PM

format the cells with 000000000000

right mouse click

first tab => then 12th option.

**MrShorty** · 06-12-2017, 02:14 PM

I would say insufficient information to suggest a solution.

One thing to note -- A csv file is all text with no formatting information. So the csv file cannot be converting a number stored as text to a number. However, Excel, when it opens a csv file, Excel's default is to convert any text in the csv file that it can interpret as a number to a number. I think a big part of debugging this sort of process is to be clear at exactly what step the "number stored as text" is being converted to a number. I expect it is the "file open" step in Excel where numbers are being converted back to numbers.

It can be important to be clear about exactly what you are trying to do. Many times with this kind of question, the OP is creating the csv file from Excel, then re-opening the newly created csv file to "checK" to see if the csv file looks right. However, the intended application for the csv file is not Excel. In these cases, because Excel can misinterpret csv data (such as converting a number stored as text to a number), I find it better to "check" that the file was created correctly by using a text editor (such as notepad or wordpad) rather than Excel.

If the intended target application is Excel, I find it preferable to use the "Import External Data -> from text" command rather than the File -> open command. When you use File->Open to open a csv file, Excel will use its default options to interpret the text file. Using the Import External data->from text command, Excel will bring up the text import wizard, which will allow you to better control the import settings for each column/field (including specifying that a given column should be treated strictly as text).

Can you clarify exactly what your problem is and what your intended target for these csv files is?

**7thMonth** · 06-12-2017, 02:48 PM

The CSV file needs to be provided to a financial institution to confirm some BI data. They are not able to use the data straight from excel so have requested a CSV file.

The IF statement requests information is one of two cells. Both cells are formated as text. In the cells are numbers like '123456789000' and '987654321000' The worksheet is popoulated with other information some text but mostly numbers. A name range is created
with the rows and columns I need. The Name range is copied to a another worksheet using:

Sheets("Worksheet Register").Range("ReportCopy").Copy
Sheets("ReportWorksheet").Range("A1").PasteSpecial Paste:=xlPasteValues

All the values are correct and 123456789000' is still a text fill. I have put in letters with number to test and they come through.

The worksheet is then saved as a CSV file. When I open the csv file (using Open Text) in excel I get 1.2E+11 instead of the full number. If I
add a letter at the end such as "123456789000A" then I get "123456789000A."

In the CSV file I want those numbers to look like the worksheet.

**MrShorty** · 06-12-2017, 03:19 PM

In the CSV file I want those numbers to look like the worksheet.

As I indicated, I would not use Excel to test this. I would open the csv file in a text editor, and verify that the file is correctly written from there.

Text (number) , then csv save changes it back to number

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Text (number) , then csv save changes it back to number

Re: Text (number) , then csv save changes it back to number

Re: Text (number) , then csv save changes it back to number

Re: Text (number) , then csv save changes it back to number

Re: Text (number) , then csv save changes it back to number

Thread Information

Users Browsing this Thread

Similar Threads

Result coming back as text not a number.....maybe?

Pasted text string appears as number with ten decimal places - How do I convert back ?

[SOLVED] Bring back certain number of characters before a particular letter or number

Fill sequential number down empty column, save last number, use as first number next time

[SOLVED] Counting Number of Times a Value is Repeated REPEATEDLY (Back to Back)

A fourth way to convert numbers saved as text back to number?

can you convert baht text back to a number?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions