Importing from CSV & Converting numbers to text

[svw]

I am importing large amounts of data that I get as a CSV and need to work with in excel. The data grows monthly, so near the end of the fiscal year I approach more than 150,000 rows of data. Once my data is imported into excel I run a Vlookup on a column in order to add a filter to my pivot table.

The data in this column is mixed text and numeric. When the data is imported from the CSV file it is stored as text. Most of the cells are strictly numeric, such as 42319813201, but a significant amount of the cells are alpha-numeric (e.g. 19070814B01). The vlookup does not work correctly unless all of the data in the column is stored as a number. When try to convert all of the cells in the column from text to numeric it takes hours. There has to be a better way to do this.

The CSV file is comma delimited and the actual data is coded as ,="42319813201" so when I specify to import a comma delimited file the value in the excel file reads ="42319813201" Then I have to insert a new row, copy the current data and paste special values to remove the equals sign and double quotation marks. The data that remains is formated as text and then I highlight the data in the column, click on the exclamation mark and click to convert text to numbers. Like I said, this takes forever. Is there a faster way?

 

[Cindy Ellis]

Assuming that the data has already been imported, the easiest solution I know of is Find and Replace. In the Find & Replace dialog, enter " as the search text, leave the Replace text empty, and choose "Replace All", then replace the " with =, and repeat the process.
Be sure that the "Look in" box is set to Formulas, and Match Entire Cell contents is NOT checked.
Hope that helps,

 

[svw]

I still end up with the same problem where the cells that contain only numbers are formatted as text. I need to be able to change 150,000 cells from text formatting to numeric formatting but not have it take all day.

I'm hoping there is a way to import a CSV file that I don't know about where I can use ,= as the delimiter and tell excel to ignore " " as text identifiers and format the entire column as numeric. Is that possible?

 

[rallcorn]

In the past I've dealt with it this way, select/highlight the column and Alt+D+E+F and the cells that contain only numbers (being treated as text) will be converted to numeric.

Another way, place "1" in another cell & copy it, then select/highlight the column you want "converted" and Alt+E+S+M and "OK". the cells that contain only numbers (being treated as text) will be converted to numeric and the cells containing alpha characters will be left alone.

Hope one of these methods will work for you.

 

[svw]

Another way, place "1" in another cell & copy it, then select/highlight the column you want "converted" and Alt+E+S+M and "OK". the cells that contain only numbers (being treated as text) will be converted to numeric and the cells containing alpha characters will be left alone.

AWESOME

Worked like a charm. In an instant!

 

[rallcorn]

Glad to help - I know from experience that it is a really annoying issue!