Excel – Converting numbers in a Column that has the ‘Scientific & General’ format

[ChaCha90]

Need assistance, is there a way to convert a ‘csv’ or ‘txt’ file that has numbers in a column that has the ‘Scientific & General’ format using Office 365? I used to use the ‘From Text/CSV’ located under the ‘Data’ tab but it does not work for me using Office 365 Excel.
I attached a ‘CSV’ and ‘TXT’ file. The 3rd tab ‘convert Scientific & General #s’ in Column ‘B’ has the numbers in the ‘Scientific & General’ format. **The 2nd tab ‘vlookup formula’ has a formula that I used as an alternate and the ‘LEGEND’ that has the Scientific numbers and what they should be when converted. Just would like to know if there was a another method/formula and if there was still a way to use the ‘From Text/CSV’ using Office 365 Excel.

 

[GraH - Guido]

Hi,

You can access the legacy text import wizard like below. Remember to activate in the options via File - > Options -> Data. Or start using Power Query. See "From Text/CSV". I recommend you try.

 

[ChaCha90]

T

Hi,

You can access the legacy text import wizard like below. Remember to activate in the options via File - > Options -> Data. Or start using Power Query. See "From Text/CSV". I recommend you try.
View attachment 75107
View attachment 75108

Thank you

 

[ChaCha90]

The 2nd part of my question is that when I use the ‘Legacy Text Import Wizard’ the numbers that were in ‘Scientific’ in Column ‘B’ do not change to reflect the number that should be displayed e.g. ‘1.01E+10 should display 10113E6’. Please see the attached ‘Legend’ for the seven Scientific numbers. Is there a ‘Custom Format or Text to Columns or VBA code or Vlookup’ that I could use to make sure the Scientific numbers convert or display per the Legend?

 

[GraH - Guido]

Maybe Import as text, not a number. It's in the text to column or text import setting when you define the columns.