Text to columns changing values. Why ?

[Ombir]

Hi Folks,

I am trying to convert values in one column by using Text to column. Strange thing that is happening is that last digits of value are being converted into some another value.Sample is given below:

Book1
	A	B	C
1	REGISTRATION	TEXT TO COLUMN	PROPER(B2)
2	11612173371	11612173371	11612173371
3	1163123029889631	1.16312E+15	1163123029889630
4	1033170320244400	1.03317E+15	1033170320244400
5	122322186801260297	1.22322E+17	122322186801260000
6	10423149365	10423149365	10423149365
7	1043193213	1043193213	1043193213
8	10441185044046	1.04412E+13	10441185044046
9	1041195433771	1.0412E+12	1041195433771
10	1042128461766200	1.04213E+15	1042128461766200
11	1042122967093000	1.04212E+15	1042122967093000
12	10421176967145086	1.04212E+16	10421176967145000
13	1041157159204245	1.04116E+15	1041157159204240
14	12243163053638137	1.22432E+16	12243163053638100
15	10411191486578327	1.04112E+16	10411191486578300
16	1043157567880465	1.04316E+15	1043157567880460
17	10431168894929883	1.04312E+16	10431168894929800
18	12242120473620295	1.22421E+16	12242120473620200
19	12242153470487539	1.22422E+16	12242153470487500
20	10421148866214648	1.04211E+16	10421148866214600
21	10521110596713255	1.05211E+16	10521110596713200
22	107415382500596358	1.07415E+17	107415382500596000
Sheet3

If we compare column A and C then there is difference between these values. Can anybody explain why this is happening ? How Can I convert values back from column C to column A ?

Thanks

 

[Rick Rothstein]

It is because you are letting "Text to Columns" treat all of the split out columns as General instead of as Text. When you get to Step 3 of 3 on the "Text to Columns" wizard, select all the columns (select the first column and Shift-click the last column) and then select the Text option button at the top left side of the dialog box... that will make the formatting of the columns Text and the numbers won't change.

 

[Ombir]

Rick,

That's okay but I want to change it into numbers not text. If you look at A2, it is not changing but A3 is changing after text to column.

 

[Rick Rothstein]

...but I want to change it into numbers not text.

No, I do not think you do... not with the size of some of your numbers. In the table you posted in Message #1, look at the value in cell A5 and then look at the value in cell C5 (which is the column you designated as being the "proper" output)... notice any difference at the end of the number? That is because Excel can only display a maximum of 15 significant digits for numeric values... anything after the 15th digit becomes zero.

 

[Ombir]

Thanks Rick. You're right, I shouldn't convert it to numbers because it may create duplicates !

 

[SpillerBD]

Yep. You hit that wall a few time you learn just because someone calls it a number doesn't mean it should be treated as number in Excel.
Account "numbers", UPS "numbers" and approval "numbers" often aren't numbers but labels or codes, ie text values.