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

[RockandGrohl]

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

="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!

 

[Special-K99]

Insert an apostrophe to indicate what follows is text?

 

[mikerickson]

Have you formatted the cells as text?

 

[RockandGrohl]

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]

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

 

[RockandGrohl]

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]

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]

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