[Excelfilter] How do I stop Excel from formatting my numbers?

April 16, 2013 11:01 AM

I sat on the phone with Microsoft support and they weren't able to help me. I've scoured the internet trying to find a solution to this problem and I'm just about ready to give up. Can Metafilter rise to the challenge? How do I stop Microsoft Excel from formatting numbers into scientific notation? No matter what I do, Excel keeps doing this. Please make it stop. See the youtube video I made explaining my problem (45 seconds).

I'm a librarian. Every couple of months I need to export new books that we have recently added to our collection from our catalog. Unfortunately the catalog gives me ISBN numbers with dashes. I need to upload these ISBN numbers to the database Novelist. Unfortunately Novelist only seems to accept ISBN numbers without dashes.

Here is the crux of my problem.
Excel WILL NOT remove the dashes without formatting the ISBN numbers into scientific notation. No matter what I do, they will be formatted after I remove the dashes. I've scoured the internet, I've called Microsoft, does anybody know how to turn this "feature" off?

If any librarians know of a better way to perform this procedure, please tell me.

posted by crios to Computers & Internet (37 answers total)

preface the numbers with a single quote (') character - that is the literal operator, meaning, don't reformat this, display it like I type it
posted by thelonius at 11:06 AM on April 16, 2013 [6 favorites]

Seconding the "preface the number with a single quote character".
posted by EmpressCallipygos at 11:06 AM on April 16, 2013

Right click the column header, Format Cells, click Number, set decimal places to 0. Does that work?
posted by theodolite at 11:07 AM on April 16, 2013 [2 favorites]

Another option is to format the cells for Text, rather than Number.
posted by Ruthless Bunny at 11:08 AM on April 16, 2013 [5 favorites]

In excel for mac, the scientific notation persists after formatting as text, like in the video. However, simply selecting the text in the edit box seems to fix it, without copying or anything. Also, pasting the numbers into cells already formatted as text seems to work as well.

(For anyone wanting to test this out, one of the problematic strings that I transcribed from the video is "9780689717833".)
posted by advil at 11:13 AM on April 16, 2013

Formatting as text doesn't help, at least not for me.

The single quote thing seems to help for me, but just in case it winds up causing unintended side effects (for example, when you export to the database, perhaps the quote gets exported too):

I've found that formatting as "Special / ZIP Code" seems to work (with a "Location" of "English /United States").
posted by Flunkie at 11:14 AM on April 16, 2013

Select the cells (or column)
Right click
"Format cells"
Select General.


Selecting text or number will reformat what's been entered.
posted by Flamingo at 11:16 AM on April 16, 2013

I think, if you WANTED the single quote to be exported to the database, you'd have to enter two of them: ''987654321

But the zip code trick is good to know, thanks
posted by thelonius at 11:17 AM on April 16, 2013

A couple of methods:

1) Use Libre Office which doesn't do the auto-formatting thing in the first place

or

2) Add an extra column and enter the formula in E1
=Text(D1,0)

Drag that down the column to get the numbers in TEXT format.
posted by Lanark at 11:22 AM on April 16, 2013

You could make your own chart in Word and see what automatic interference MS would annoy you with there.
posted by Cranberry at 11:24 AM on April 16, 2013

This may be overkill, but to parse the sample "978-3-16-148410-0" in cell A1, for example, you could write "=CONCATENATE(LEFT(A1,3),MID(A1,5,1),MID(A1,7,2),MID(A1,10,6),RIGHT(A1,1))"

No worries about text/not text, scientific notation, etc.
posted by milestogo at 11:28 AM on April 16, 2013

Try putting this:

=TEXT(SUBSTITUTE(D2,"-",""),"#")

in the column beside the isbn number with the dashes. The formula assumes the ISBN is in column D, and begins with row 2. You should be able to just drag the formula down and it will propagate to the cells underneath.
posted by Mooski at 11:33 AM on April 16, 2013 [1 favorite]

Excel can automatically remove the dashes with the SUBSTITUTE() function. For example, cell D1 in your video is "978-0-689-71783-3". Enter:

=SUBSTITUTE(D1,"-","")

into cell E1, and the dashes are removed. This is the result, copied and pasted from Excel:

9780869717833

No scientific notation. Ta-da!
posted by whitecedar at 11:37 AM on April 16, 2013 [1 favorite]

Ah, duplicate answer. Sorry about that.
posted by whitecedar at 11:38 AM on April 16, 2013

I watched your video...

I work with Excel and ISBN numbers daily. Good call to use the Find/Replace to remove the dashes.
The best way I know how to stop Excel from formatting into scientific notation, is to put an apostophe (') at the start of the cell ahead of the number, as thelonius has described in the first comment. It's a bit of a pain since you would have to go cell by cell, but I haven't found any easier way.
posted by MeatheadBrokeMyChair at 11:41 AM on April 16, 2013

It's a bit of a pain since you would have to go cell by cell, but I haven't found any easier way.

I can help you!

Here's a fun thing!

Put ="'"&A1 (double quote-apostrophe-double quote) in the next column. Copy and paste down your list of numbers. Copy, paste special values the entire column. This will add the ' to your ISBN numbers.
posted by Ruthless Bunny at 11:45 AM on April 16, 2013 [1 favorite]

I think your find-replace method for getting rid of the hyphens is fine, as demo'ed in the video it works. No need for the fancy formulas there.

Then, if you highlight that column and then do Format/Number, and select "Number" (not "General") and choose 0 decimal places and no separators, your scientific notations will change back to regular number strings. As long as the database Novelist will accept them in number format rather than text, you should be good to go.
posted by beagle at 11:46 AM on April 16, 2013

Response by poster: I've been trying the "=SUBSTITUTE(D1,"-","")" and other "E1" methods and nothing seems to be happening. The cell, E1, remains empty.
posted by crios at 11:46 AM on April 16, 2013

Is it OK to leave them a s number?

After removing the hyphens go to Format Cells/Custom and replace 'General' with 13 zeroes:

0000000000000

smaller numbers will display with a leading zeros
posted by tinker at 11:46 AM on April 16, 2013

Okay, one more thing - try formatting as a number with zero decimal points and no comma rather than as text.
posted by Mooski at 11:48 AM on April 16, 2013

I haven't found formatting the column as text or general to be completely reliable. Maybe I am doing it wrong.

Excel just aggressively wants you to be working with numbers, although more people probably use it as some kind of database-graph paper hybrid. The nastiest example I have seen related to your question was Excel assuming that a column with room numbers such "11-14" should be converted to date values where possible, o the spreadsheet now showed "Nov-13" for a room.
posted by thelonius at 11:48 AM on April 16, 2013

Response by poster: Unfortunately I can't use the number formatting because it deletes out any preceding zeroes.
posted by crios at 11:49 AM on April 16, 2013

Do ISBNs ever have leading zeros? If so, you need Text.
posted by thelonius at 11:49 AM on April 16, 2013

Response by poster: Tinker - I tried the 13 0s but that doesn't work because it inserts 0s. The ISBN numbers be either 10 or 13 digits long.
posted by crios at 11:50 AM on April 16, 2013

Response by poster: the single ' might be the best thing to do. I have a spreadsheet with over 500 ISBN numbers. Does anybody know how to add a preceding ' in each cell?
posted by crios at 11:59 AM on April 16, 2013

Where are these numbers coming from before they end up in Excel? (apologies if this was in the video, but I can't hear sound on this computer)

If it's CSV, you can stick an equals sign in front of each field, that is:

"Other Column 1", "0000001", "Other Column 2"

becomes

"Other Column 1", ="0000001", "Other Column 2"

Does anybody know how to add a preceding ' in each cell?

If I have to do this in Office, I end up throwing everything in Word, and doing a Find and Replace. Hit the "more" button, and then pick "Paragraph Mark" from the "Special" button. You want to replace ^p with ^p'. Make sure it doesn't use smart quotes.
posted by Nonsteroidal Anti-Inflammatory Drug at 12:04 PM on April 16, 2013

Put ="'"&A1 (double quote-apostrophe-double quote) in the next column. Copy and paste down your list of numbers. Copy, paste special values the entire column. This will add the ' to your ISBN numbers.
posted by Ruthless Bunny at 12:04 PM on April 16, 2013 [1 favorite]

Best answer: Excel text formatting for numbers can behave strangely, especially when you are doing something like find/replace. It shouldn't be converting these numbers to scientific notation if the cell is formatted as text. When I replicate what you are doing, I get the same result, but when I hit F2 to edit the text, then immediately hit enter, it converts it back to a number displayed as text, as it should. I suspect this is a bug.

Have you tried pasting the ISBNs into notepad first, then find/replace the dashes, then pasting the result into cells already formatted as text? This works fine for me.
posted by [expletive deleted] at 12:06 PM on April 16, 2013

Response by poster: Copy and pasting in notepad and then finding and replacing was the simplest way to get the job done. Thanks.

It really shouldn't be that hard.
posted by crios at 12:13 PM on April 16, 2013

If you find yourself needing to modify the formatting of numbers in a way more complicated than excising dashes, I recommend downloading notepad++ and getting to know its regular expressions search feature. I came to lean on this heavily in an old job, and it now comes in handy all the time at work and home.

For example, say you have a list of ISBNs that have all kinds of other characters, like trailing or leading spaces, slashes, notes at the end of a line, etc. You can easily reduce that just to a list of numbers in Notepad++ searching for this pattern: [^\r\n\d] and replacing with nothing.
posted by [expletive deleted] at 12:19 PM on April 16, 2013

How did your information get into Excel in the first place?

What file format does your database want its upload in?

If, as I suspect, your catalog software exports its recently-added list as a CSV (comma separated values) file, and your database software wants that same format for upload but without the dashes in the items in the fourth column, you might be better off not ever opening the CSV with Excel and using some other tool to reformat it for you.

Could you make available online a completely unprocessed copy of something that came out of your catalog, along with the corresponding file you're currently using Excel to generate for database upload? If you can do that, I'll write you a tiny conversion script that slurps up one and emits the other.
posted by flabdablet at 12:25 PM on April 16, 2013 [1 favorite]

Oh, and it should also be very easy to have it convert all 10-character ISBNs to their 13-character equivalents while it was at it, in case you'd rather use the newer format consistently.
posted by flabdablet at 12:28 PM on April 16, 2013

Instead of formatting it as text, like everyone is suggesting, consider formatting it as a number.

Some people are misinformed in this thread - Excel will display numbers in scientific notation if you have the formatted as "General". If you set the format as "Number" it will not use scientific notation. You will also have to click the "Decrease decimal" button twice so that it doesn't show decimal places.

If you want to format it as text, choosing "Text" as the cell format will not do the trick. This will only format cells as text AFTER you set the cell format and then type into the formatted cells. Instead, the easiest way is to use a formula: (assuming A1 is the address of the first ISBN cell)

=TEXT(A1,"0")

I use 11-14 digit UPCs in Excel all the time so I've had to deal with this often.
posted by zixyer at 2:08 PM on April 16, 2013

Upon further review, perhaps it would save you a few steps if you just wanted to use a formula to both remove the dashes and format the result as text. The formula for this would be:

=SUBSTITUTE(A1,"-","")

If you need to prepend zeroes so that the result is always 13 digits, you would use

=TEXT(SUBSTITUTE(A1,"-",""),"0000000000000")

(there are 13 zeroes in the above formula)
posted by zixyer at 2:12 PM on April 16, 2013

It's a while since I have had to deal with this sort of thing in anger, but is there a reason you are insisting on using Excel/any spreadsheet?

A quick look at the EBSCO Novelist upload page suggests that they expect tab delimited .txt or comma delimited .csv input. I don't know what your catalog is generating, but unless it really is a binary Excel file (what the last system I used claimed was an "Excel" output file was really csv) you might be better off ignoring Excel and all it's issues.

If the output file is simple enough, a text editor would easily strip dashes. For more complex transformations, one of the free csv editing tools would probably work better, but either route would likely be better and simpler than taking csv, importing it into a spreadsheet, editing it (with associated formatting etc. gotchas), then re-exporting it.
posted by Quinbus Flestrin at 4:46 PM on April 16, 2013 [1 favorite]

I'm with Quinbus Flestrin. There are times when just creating a text file is easiest.
posted by The Light Fantastic at 6:15 PM on April 16, 2013

If you need to prepend zeroes so that the result is always 13 digits

That's not how the 13 digit version of a 10 digit ISBN works.
posted by flabdablet at 12:17 AM on April 17, 2013