Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Trisha Lynn
 
Posts: n/a
Default Removing Scientific Notation

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?
  #2   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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

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

In article ,
"Trisha Lynn" <Trisha wrote:

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?

  #3   Report Post  
Trisha Lynn
 
Posts: n/a
Default

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?

"JE McGimpsey" wrote:

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

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

  #4   Report Post  
Trisha Lynn
 
Posts: n/a
Default

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" wrote:

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

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

  #5   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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.

In article ,
"Trisha Lynn" wrote:

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?



  #6   Report Post  
Trisha Lynn
 
Posts: n/a
Default

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" wrote:

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

  #7   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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

In article ,
"Trisha Lynn" wrote:

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.

  #8   Report Post  
Trisha Lynn
 
Posts: n/a
Default

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" wrote:

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).

  #9   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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").

In article ,
"Trisha Lynn" wrote:

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?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
removing duplicate records in excel, how to do it? Don Excel Discussion (Misc queries) 3 July 27th 05 01:45 AM
How do I stop excel removing zeros? tomcat Excel Discussion (Misc queries) 2 January 21st 05 06:40 PM
convert scientific notation to a number Peter Excel Discussion (Misc queries) 1 January 4th 05 08:08 PM
Removing ' character from cells Don Excel Discussion (Misc queries) 5 December 21st 04 06:41 PM
Removing Multiple Hyperlinks Olly New Users to Excel 0 November 29th 04 12:17 PM


All times are GMT +1. The time now is 06:09 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"