Removing Scientific Notation

[Trisha Lynn]

I'm a data entry clerk who works with UPC codes a lot. I'm trying to import
an Excel spreadsheet table into Access to check it against our database for
missing items, but I find that an error/problem at the Excel level is keeping
me from doing this.

The problem is that the data from a certain company is being given to us
with spaces after the first, sixth and eleventh digits--probably to avoid
this very problem that I'm having. When you take out the spaces manually,
the entry reverts to scientific notation. Even after formatting the cells
both before or after the spaces are taken out does the data come out as
scientific notation.

The most ironic thing is that this only happens with 12 digit numbers (which
most UPC codes are). In cases where there's a 0 at the beginning of the
12-digit UPC code, the number is reduced to 11 digits--but is in numeral
form, not scientific notation form. This leads me to conclude that there's
some trigger that's being turned on at the 12-digit or character mark that's
causing the number to automatically switch into scientific mode.

How do I turn this off? Please keep in mind that I'm a data entry clerk,
not a computer programmer or code generator, so if the solution involves
monkeying around with code, can you please explain very slowly? Like you
would to a high school senior?

 

[JE McGimpsey]

Before you edit the spaces, format the cells as Text.

Then you can use Replace to replace a single space with nothing.

 

[Trisha Lynn]

I already did that:

<<Even after formatting the cells both before or after the spaces are taken
out does the data come out as scientific notation.>>

Do you have any other suggestions?

 

[Trisha Lynn]

I already did that (as I said in my initial post). I just tried it again and
it's doing the same thing.

Do you have another suggestion?

 

[JE McGimpsey]

How are you manually editing the Text-formatted cell? When I try it, the
combined number stays Text.

Sorry, I didn't interpret

Even after formatting the cells both before or after the spaces are
taken out does the data come out as scientific notation.

as meaning that you'd formatted the number as Text first.

 

[Trisha Lynn]

Usually, I go into the format bar and manually edit. And yes, when you do it
that way, the combined number will stay Text. For five or ten entries, I can
spend the time to do that.

But I work with spreadsheets with many UPCs on them from this company and to
manually edit 10 to 50 UPCs would take much longer than if I were to do the
"Find/Replace" method you suggested earlier--which would be more optimal to
me.

 

[JE McGimpsey]

Sorry, when I wrote that, I'd forgotten that I'd been using the
configuration which replaced the Replace command with a custom version
(which keeps Text text - I've had similar problems and rarely use the
Replace command for anything else).

FWIW, here's a stripped down macro that you can use:

Public Sub RemoveInteriorSpacesFromTextNumbers()
Dim rCell As Range
For Each rCell In Selection
With rCell
.NumberFormat = "@"
.Value = Application.Substitute(.Text, " ", "")
End With
Next rCell
End Sub


If you're unfamiliar with macros, see David McRitchie's "Getting Started
with Macros":

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Trisha Lynn]

That works out just nicely. One last question:

I'm a little confused by the page you sent me to. With a macro like this
that I'm going to be using over and over again, where would I save it and how?

 

[JE McGimpsey]

The easiest place is to save it in your Personal.xls file (Personal
Macro Worbook for MacXL). David's page has an explanation ("Installing a
Macro into your Personal.xls").