Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#3
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
removing duplicate records in excel, how to do it? | Excel Discussion (Misc queries) | |||
How do I stop excel removing zeros? | Excel Discussion (Misc queries) | |||
convert scientific notation to a number | Excel Discussion (Misc queries) | |||
Removing ' character from cells | Excel Discussion (Misc queries) | |||
Removing Multiple Hyperlinks | New Users to Excel |