Text file import to excel

%3CLINGO-SUB%20id%3D%22lingo-sub-2152596%22%20slang%3D%22en-US%22%3EText%20file%20import%20to%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2152596%22%20slang%3D%22en-US%22%3EI%20import%20a%20text%20file%20into%20excel%20with%20about%2060%20employees%20and%20have%20no%20issues.%20Sometimes%20when%20I%20import%20a%20similar%20file%20with%20900%20employees%2C%20I%20noticed%20some%20of%20the%20amounts%20do%20not%20appear%20as%20numbers.%20Instead%20the%20data%20appears%20as%20counts.%20When%20I%20try%20to%20convert%20to%20number%20does%20not%20work.%20I%20would%20appreciate%20help%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2152596%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2152791%22%20slang%3D%22en-US%22%3ERe%3A%20Text%20file%20import%20to%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2152791%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F973371%22%20target%3D%22_blank%22%3E%40AhmedHos1986%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20wrote%3A%20%3CEM%3ESometimes%20when%20I%20import%20a%20similar%20file%20with%20900%20employees%2C%20I%20noticed%20some%20of%20the%20amounts%20do%20not%20appear%20as%20numbers.%20Instead%20the%20data%20appears%20as%20counts.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhich%2C%20frankly%2C%20is%20a%20bit%20confusing.%20%3CSTRONG%3ECounts%3C%2FSTRONG%3E%20normally%20being%20%3CSTRONG%3Enumbers%3C%2FSTRONG%3E%2C%20after%20all.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20went%20on%20to%20say%2C%20%3CEM%3EWhen%20I%20try%20to%20convert%20to%20number%20does%20not%20work.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20don't%20specify%20how%20you're%20doing%20that%20conversion.%20If%20it%20is%20to%20just%20change%20format%2C%20or%20re-state%20the%20cells'%20formats%20as%20%22Number%22%20that%20may%20not%20succeed.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20suggest%20you%20try%20using%20a%20helper%20column%20and%20entering%20there%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DVALUE(CellWithCount)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EThen%20copy%20that%20down%20the%20parallel%20%22helper%20column.%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20sometimes%20find%20I%20have%20to%20convert%20numeric%20text%2C%20when%20imported%2C%20to%20the%20value%20of%20the%20number%20by%20that%20process...concluded%20by%20doing%20a%20%3CU%3E%3CSTRONG%3ECopy....Paste%20Special...Values%3C%2FSTRONG%3E%3C%2FU%3E%20back%20over%20the%20offending%20column.%20I%20do%20a%20lot%20of%20imports%20from%20bank%20accounts%2C%20and%20am%20surprised%20occasionally%20that%20numbers%20there%20are%20exported%2Fimported%20as%20text.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2152796%22%20slang%3D%22en-US%22%3ERe%3A%20Text%20file%20import%20to%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2152796%22%20slang%3D%22en-US%22%3EThat%20makes%20sense.%20I%20will%20try%20a%20helper%20column.%20Usually%20when%20I%20select%20a%20set%20of%20data%2C%20there%20is%20an%20icon%20that%20appears%20and%20it%20allows%20conversion%20to%20number.%20I%20work%20with%20employee%20benefits.%20Usually%20to%20double%20check%20my%20amounts%20i%20select%20data%20in%20my%20column%20to%20compare%20totals.%20In%20this%20case%20after%20the%20import%20when%20I%20selected%20the%20column%20it%20was%20not%20giving%20sum%20of%20the%20data%20and%20instead%20the%20data%20in%20that%20column%20seemed%20like%20they%20were%20text.%3C%2FLINGO-BODY%3E
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