I've been working with domestic and International call records for over 20 years and run into this almost weekly. My solution, when you are only going to convert 1, 2 or 3 columns of long numbers, is to load the CSV normally, then highlight the column and do the next series of key strokes (yes, i've memorized them, so I can do them quite fast) , a, e, , , t, Do NOT hold down any of these keys, simply strike the individual key. YOU CAN ONLY CONVERT ONE COLUMN at a time with this method. This doesn't deal with the Char(160) issue. You will lose leading zeros if they were in your source file.
Fantastic Bill. Thank-you so much. And like many of your videos this one has little gems that are not the direct thrust of the lesson. In this case it was using multiple delimiters with the import wizard.
A) Who in the world could give this a thumbs down? B) In what decade will Microsoft make it possible to "turn off" scientific notation? SN has to be useful to .01% of Excel users and annoying to the rest.
Done on the vote. Though I wish the request wasn't just for "text" numbers. Sometimes I have an actual number that happens to be large and even when manually entered, it defaults to Sci Notation. Yes a manual entry can then be formatted to text, but why force us to do an extra step that now makes it so I can't have it formatted as a nice number with commas without having to add them in "manually" (through a formula/script). Just stick to the Data Entry Form Design 101 concept of Not-Changing-User-Inputted-Values-Without-Direct-Consent I work on medical software and if we converted values to scientific notation, we'd be breaking federal record keeping rules... so yeah. Don't mess with stuff.
😮💨 I will be crazy 2 days I couldn't find a solution, I have a file contains barcode, any solution I try, it is not working, once I close the file then open again, the numbers are converted again to scientific notation
Bam this is great. I deal with 23-digit product numbers, I see this sort of CSV issue sometimes. Now if I could get people to stop making CSV files in the first place.
Hi there, is this the problem i experiences, i use Fluke CSV-files where the conversion in 365 gives me exponetial nr like 3e-006, 6,3e-005 etc, and i cannot do anything with these nr.. if i try to change them into digital, it just doesent do anything, nor will it let me make a graf, just tells med that the data is invalid??? ????- best regards / A, and a happy new year
I am very great full to you because i am looking for a solution about a problem regarding a text file with a large field converting into excel file. Thanks a lot Allah bless you.
Microsoft rolled this new feature out to their Microsoft 365 customers a month ago. If you have Microsoft 365, you can join Office Insiders at the beta level and try it now. If you are stuck with Office 2019 or Office 2021, then you won’t get it until Office 2024.
Need your help... When I convert scientific notation to decimal, last 4 digit becomes 0000. It's not providing accurate value. Example - 123456789123456789 Scientific notation - 1.23457E+17 Decimal value - 123456789123456000(using trim function) Please help
File, Options, Data. In the Legacy connectors, choose Text/CSV. Then Data, Get Data, Legacy, CSV. This takes you through the Text Import Wizard. In the 3rd step, choose the column that is being converted and choose Text.
I've been working with domestic and International call records for over 20 years and run into this almost weekly.
My solution, when you are only going to convert 1, 2 or 3 columns of long numbers, is to load the CSV normally, then highlight the column and do the next series of key strokes (yes, i've memorized them, so I can do them quite fast)
, a, e, , , t, Do NOT hold down any of these keys, simply strike the individual key.
YOU CAN ONLY CONVERT ONE COLUMN at a time with this method. This doesn't deal with the Char(160) issue. You will lose leading zeros if they were in your source file.
Fantastic Bill. Thank-you so much. And like many of your videos this one has little gems that are not the direct thrust of the lesson. In this case it was using multiple delimiters with the import wizard.
This just got better and better as the video went on!
Thank you Jefferson Wright. I am glad to hear the video was helpful.
Awesome video filled with many fun tips!
A) Who in the world could give this a thumbs down? B) In what decade will Microsoft make it possible to "turn off" scientific notation? SN has to be useful to .01% of Excel users and annoying to the rest.
Done on the vote. Though I wish the request wasn't just for "text" numbers. Sometimes I have an actual number that happens to be large and even when manually entered, it defaults to Sci Notation. Yes a manual entry can then be formatted to text, but why force us to do an extra step that now makes it so I can't have it formatted as a nice number with commas without having to add them in "manually" (through a formula/script). Just stick to the Data Entry Form Design 101 concept of Not-Changing-User-Inputted-Values-Without-Direct-Consent
I work on medical software and if we converted values to scientific notation, we'd be breaking federal record keeping rules... so yeah. Don't mess with stuff.
😮💨 I will be crazy
2 days I couldn't find a solution,
I have a file contains barcode, any solution I try, it is not working, once I close the file then open again, the numbers are converted again to scientific notation
Bam this is great. I deal with 23-digit product numbers, I see this sort of CSV issue sometimes. Now if I could get people to stop making CSV files in the first place.
Great video, thank you! I use the same method to preserve leading zeroes in numbers.
Thanks Mr. Excel
Thank you. Very useful tips.
Very useful video, thanks a lot
From Holland again a nice tip!!!
Hi there, is this the problem i experiences, i use Fluke CSV-files where the conversion in 365 gives me exponetial nr like 3e-006, 6,3e-005 etc, and i cannot do anything with these nr.. if i try to change them into digital, it just doesent do anything, nor will it let me make a graf, just tells med that the data is invalid??? ????- best regards / A, and a happy new year
I am very great full to you because i am looking for a solution about a problem regarding a text file with a large field converting into excel file. Thanks a lot Allah bless you.
Parabéns, muito bom. Obrigado.
very helpful, thanx
Any reason you can't just copy the non-breaking space from the front of the number, before doing the replace?
any update on the link to vote for not changing to scientific notation? shows account closed.
Microsoft rolled this new feature out to their Microsoft 365 customers a month ago. If you have Microsoft 365, you can join Office Insiders at the beta level and try it now. If you are stuck with Office 2019 or Office 2021, then you won’t get it until Office 2024.
But in last trick, we still get file with xls extension. How to get these result for csv extension
Wonderfull
Need your help... When I convert scientific notation to decimal, last 4 digit becomes 0000. It's not providing accurate value.
Example - 123456789123456789
Scientific notation - 1.23457E+17
Decimal value - 123456789123456000(using trim function)
Please help
Correct. If the number is too long, it will often lose the last 4-8 digits and display it as 000000.
A big like free of char 160!
Voted :)
bill jelen plz make this for mac users
Bill Jelen ok thanks
didnt get anything
get to the point! omf%gggggvgggvv you know l love you like a brother Bill, but how do l get rid of scientific notation?!
File, Options, Data. In the Legacy connectors, choose Text/CSV.
Then Data, Get Data, Legacy, CSV. This takes you through the Text Import Wizard. In the 3rd step, choose the column that is being converted and choose Text.
@@MrXL much obligued sir!