Stop Excel changing what it thinks is scientific notation to a long number.

RockandGrohl

Active Member
Joined
Aug 1, 2018
Messages
499
Office Version
  1. 2010
Platform
  1. Windows
Unfortunately this was bound to happen sooner or later.

We have a number of product codes that are 6-digit numerical or alpha-numerical codes.

It can be like "206679" or "F3465D" etc etc.

Owing to the powers that be, we now have numbers like "716E18"

Excel sees this and automatically converts it from a string, to a scientific character, so 716000000000000000000

To fix this, our IT guy changed the extract where ALL tours are held, to be like this

Code:
="716E18"

Which, when then copied to our various sheets that deal with these numbers, fail to read. Is there any way to change ="716E18" to just 716E18, without Excel changing it to scientific? These codes will be in amongst all other codes, both numeric and alpha numeric.

If I convert the numbers to text, it breaks the ability for them to be looked up in a multitude of other sheets.

Thanks!
 
Last edited:

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

RockandGrohl

Active Member
Joined
Aug 1, 2018
Messages
499
Office Version
  1. 2010
Platform
  1. Windows
Have you formatted the cells as text?


The trouble is if the cell is numeric, it breaks the ability to look up later down the line.

The problem here is that in one column we can have three different strings. Numeric, Alpha-numeric and then this alpha-numeric as scientific.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,916

ADVERTISEMENT

Unless you are doing math on product codes, their being stored as text should have no problem down stream.
 

RockandGrohl

Active Member
Joined
Aug 1, 2018
Messages
499
Office Version
  1. 2010
Platform
  1. Windows
Insert an apostrophe to indicate what follows is text?

That would prevent those product codes being used in 100% of our sheets, which is probably about 60-70 different sheets.

The easy answer here is to just stop using these product codes, but unfortunately they are auto-generated.


What happens is all product codes and information is loaded to "Tour Manager Extract.csv", this is some 15,000 lines long.

Because the problematic codes were being converted to Scientific at the point of generation, as a quick fix the IT guy surrounded it in quotes to force it to stay as a 6 digit code.

Most of our macro sheets go into this Tour Manager Extract to pull the list of products, so these need to be converted so they can be read from other sheets. If I paste to values as text, all of the related lookups etc will fail. So if I do "Range("A2:A" & Lastrow).value = Range("A2:A" & Lastrow).value" it converts all numbers to number, text to text, but the 716E18 products are converted to scientific.


I think what I need to do is a loop where each cell in turn is checked to see if it has an alpha character in, if it does, then change the formatting to text, otherwise it's number. Hopefully that way it will work.
 

RockandGrohl

Active Member
Joined
Aug 1, 2018
Messages
499
Office Version
  1. 2010
Platform
  1. Windows
Unless you are doing math on product codes, their being stored as text should have no problem down stream.

As a quick test, let's say you have two sheets, Tour Manager Extract and Advertisement Plan.

In Advertisement Plan, and 50+ other sheets, you have a long list of product codes, some of them are fully numeric, like "123456"

If, in Tour Manager Extract, "123456" is stored as Text, then in Advertisement Plan 123456 as a number won't be able to be used as the basis for a Vlookup, it would also need to be converted to text.


I need a solution where Tour Manager Extract, at the point of being read or copied, needs to have each individual line checked for an alpha character, and if found, the cell converted to text, otherwise converted to number.
 

RockandGrohl

Active Member
Joined
Aug 1, 2018
Messages
499
Office Version
  1. 2010
Platform
  1. Windows
I'm doing this at the moment which only takes 0.5 seconds so no big deal.

Do Until Cells(ActiveCell.Row, "A").Value = ""


If Cells(ActiveCell.Row, "A").Value Like "*[A-Z]*" Then
ActiveCell.NumberFormat = "@"
Else
ActiveCell.NumberFormat = "0"
End If


ActiveCell.Offset(1, 0).Activate
Loop
 

Watch MrExcel Video

Forum statistics

Threads
1,122,940
Messages
5,598,975
Members
414,270
Latest member
skipolmsted

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top