Text file import to excel

New Contributor
I import a text file into excel with about 60 employees and have no issues. Sometimes when I import a similar file with 900 employees, I noticed some of the amounts do not appear as numbers. Instead the data appears as counts. When I try to convert to number does not work. I would appreciate help
4 Replies

@AhmedHos1986 

 

You wrote: Sometimes when I import a similar file with 900 employees, I noticed some of the amounts do not appear as numbers. Instead the data appears as counts.

 

Which, frankly, is a bit confusing. Counts normally being numbers, after all.

 

You went on to say, When I try to convert to number does not work.

 

You don't specify how you're doing that conversion. If it is to just change format, or re-state the cells' formats as "Number" that may not succeed. 

 

I suggest you try using a helper column and entering there

=VALUE(CellWithCount)

Then copy that down the parallel "helper column."

 

I sometimes find I have to convert numeric text, when imported, to the value of the number by that process...concluded by doing a Copy....Paste Special...Values back over the offending column. I do a lot of imports from bank accounts, and am surprised occasionally that numbers there are exported/imported as text.

That makes sense. I will try a helper column. Usually when I select a set of data, there is an icon that appears and it allows conversion to number. I work with employee benefits. Usually to double check my amounts i select data in my column to compare totals. In this case after the import when I selected the column it was not giving sum of the data and instead the data in that column seemed like they were text.

@AhmedHos1986 

 

Let me know if it does the trick.

 

I'm retired now, but during my career spent several years as the director of the HR and Payroll database for a major corporation......so I know the kind of data you're dealing with intimately.

Thanks I really appreciate it. It worked