+ Reply to Thread
Results 1 to 15 of 15

Need a function to list book ISBN numbers.

  1. #1
    Registered User
    Join Date
    12-30-2010
    Location
    somewhere, USA
    MS-Off Ver
    Excel 2010
    Posts
    86

    Need a function to list book ISBN numbers.

    I'm trying to create a flat database in Excel to list all my books.The problem is how can I create a function that takes care of the ISBN numbers that books have? Sometimes they have 9 digit numbers, sometimes 10 or even 13 numbers. I need a function that looks in a column that has unformatted numbers and then takes the MID function to convert them into strings and then CONCATENATE them into their appropriate number format like 0-0000-9999-9. It seems that simply formatting the column using custom format doesn't work.

    A1 is Title.
    B1 is Year
    C1 is Format
    D1 is where I list the unformatted ISBN number.
    E1 is the function that looks in D1 and uses the MID and CONCATENATE to create the appropriate ISBN number regardless if it's 9 digit, 10 digit or 13 digit.

    Can anybody create a sample function that does this? Thanks in advance.
    Last edited by DorothyFan1; 01-18-2011 at 01:49 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Need a function to list book ISBN numbers.

    Can you list some example entries in column D and the results you are seeking?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    12-30-2010
    Location
    somewhere, USA
    MS-Off Ver
    Excel 2010
    Posts
    86

    Re: Need a function to list book ISBN numbers.

    Quote Originally Posted by NBVC View Post
    Can you list some example entries in column D and the results you are seeking?
    Here it is. The problem is when I enter the ISBN numbers raw...Excel reformats the number using the scientific equation notation. So I have to prefix the number with the apostrophe. Therefore the function needs to convert the text to a string that gives an ISBN number using this format

    xxx-x-xxxx-xxxx-x and that's only if the ISBN number is 13 digits in length.
    Attached Files Attached Files

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Need a function to list book ISBN numbers.

    If you format the cell as Number with no decimals, it will show the whole number (as long as it is less than 15 digits).

    If you custom format the cell as: 000-0-0000-0000-0 does it work?

    Not sure what you mean by: and that's only if the ISBN number is 13 digits in length ?

  5. #5
    Registered User
    Join Date
    12-30-2010
    Location
    somewhere, USA
    MS-Off Ver
    Excel 2010
    Posts
    86

    Re: Need a function to list book ISBN numbers.

    Sometimes the length of the ISBN number can be *less* than the 13 digit equivalent. Usually the older books have the earlier format of either 9 or 10 digit ISBN numbers.

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Need a function to list book ISBN numbers.

    So does the formatting I showed work for all your numbers? If not what are examples of how some 9 and 10 digits numbers should appear.

  7. #7
    Registered User
    Join Date
    12-30-2010
    Location
    somewhere, USA
    MS-Off Ver
    Excel 2010
    Posts
    86

    Re: Need a function to list book ISBN numbers.

    Quote Originally Posted by NBVC View Post
    So does the formatting I showed work for all your numbers? If not what are examples of how some 9 and 10 digits numbers should appear.
    I've tried the formatting you suggested and it doesn't work. When I reference the column that has the raw text string it doesn't put the string into the format I want it to read with the dashes. It simply grabs the text string and just puts it into the cell.

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Need a function to list book ISBN numbers.

    I was showing you how you wouldn't need the extra column to convert it. You can direcly apply to the original ISBN if you enter it without the apostrophe.

    But if you want to keep it that way and want the extra column, then try:

    =TEXT(D2+0,"000-0-0000-0000-0")

  9. #9
    Registered User
    Join Date
    12-30-2010
    Location
    somewhere, USA
    MS-Off Ver
    Excel 2010
    Posts
    86

    Re: Need a function to list book ISBN numbers.

    Thanks! Now it works! I didn't think it would...but it works perfectly now. The only problem left is figuring out what to do with ISBN numbers that don't reach 13 digits. How do I enter those numbers and have Excel enter the trailing zeroes to accommodate the 13 digit format for that column?

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Need a function to list book ISBN numbers.

    Now, I think we are back to having a separate formula in E2:

    Here is one way to accommodate 9 to 13 digits:

    Please Login or Register  to view this content.
    There could be a shorter way.....

  11. #11
    Registered User
    Join Date
    12-30-2010
    Location
    somewhere, USA
    MS-Off Ver
    Excel 2010
    Posts
    86

    Re: Need a function to list book ISBN numbers.

    Thanks a bunch for the formula. This is terrific. You're fantastic. Thanks! I'll mark the thread as solved.

    I spoke too soon. I tested the function and it's not updating to accommodate new entries that don't list ISBN numbers that aren't 13 digits long. So if I enter an ISBN number less than 13 digits the function doesn't look at the entry. Or does this function require that the cell in D2 be formatted as text?
    Last edited by DorothyFan1; 01-18-2011 at 01:09 PM.

  12. #12
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Need a function to list book ISBN numbers.

    The entries in column D are formatted as Number with 0 decimals...

    See attached...
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    12-30-2010
    Location
    somewhere, USA
    MS-Off Ver
    Excel 2010
    Posts
    86

    Re: Need a function to list book ISBN numbers.

    Marked the thread as solved. Thanks for the solution.

  14. #14
    Registered User
    Join Date
    02-15-2020
    Location
    delhi
    MS-Off Ver
    2007
    Posts
    1

    Angry Re: Need a function to list book ISBN numbers.

    I am unable down load above excel

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,382

    Re: Need a function to list book ISBN numbers.

    Why is that? The attachment opens for me.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1