Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

'text to columns' conversion error with scientific notation


I follow the dialog to convert group of data (mix of alpha-numerics or just numerics) in a single into columns.

the data is already entered as 'text' weather aphs-num or just num.

the text to col conversion does not always convert the group of 'values' in the cell into text, as expected, but displays the numerals as i.e 5.67E+12

worksheet attached has a very small example set

any suggestions or is this Excel2010 issue?

any other ideas to convert/accomplish my goal



Selected Answer

This issue is quite annoying but there is a little trick to help you.

Expand all of the parts using Text to Columns and then select all of the results and right-click > Format Cells > Number tab > Custom category > type 0 into the Type: input area and hit OK.

That should convert all numbers from scientific notation, which is the issue in your case, to their full version and retain the look of the text part numbers as well.



yes don, i forgot that 'hint'...reason i dont do it is/was unknowing to you that i sort my table, accending order, (yes you can select if it looks like a number, sort as number vs if number as number if text as text).  then you have those silly formulas that deterine if it is a text or not ... and produce T/F.  depends on final use once/after the conversion...as far as the conversion your answer was dead on !
Again thanks

(FYI  still cant vote up/dwn for answers ... )
jhixy (rep: 2) Aug 5, '16 at 1:01 pm
Glad it worked! And voting is only allowed after you get so many points because, otherwise, some people with accounts would just vote like crazy for no reason.
don (rep: 1979) Aug 5, '16 at 1:08 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login