Leading Zero(s) / Custom Format? (Solved)

[ZapBrain]

Hey all *Love this Board*

I'm having trouble with the leading zeros of my ISBN#s; excel keeps chopping them off. I found a topic that discussed this issue, but it won't work for me.

http://www.mrexcel.com/board2/viewtopic.php?t=75303

I can't format them as text because I'm getting them as an isbn with dashes (ie:978-05689-256-7) and when I remove the dashes (even if its formatted as text) excel changes the number to 9780568.9E+12 (or something like that)

I think the solution would be to create a custom format (ie. 0000000000) to preserve the zeros, but I'm having 2 problems:

1. Since the ISBN#'s have 2 different formats (ISBN-10 & ISBN-13) I need to somehow check if the first 3 numbers are 978 then based on that it should be formatted either as ISBN-10 (0000000000) or ISBN-13 (0000000000000)

2. In the post that I linked to above it said that I won't be able to do a vlookup if its custom formatted. Is there any way around this?

 

[Lewiy]

Can you give some examples of the numbers and how you want them displayed. I'm most confused firstly about how the leading zeros are incorporated into the number if starts with 978. Also the one example number you have given seems to have 11 digits which is not 10 or 13.

 

[ZapBrain]

Ok, Sorry if I wasn't clear.

Original | After Find/Replace - (frmt Text) | Need to Look Like
---------- ---------------- ------------------
978-056879-256-7 | 9.78057E+12 (incorrect) | 9780568792567
978-09769161-9-2 | 9.78098E+12 (incorrect) | 9780976916192
1-58330-981-7 | 1583309817 (correct) | 1583309817
0-87306-866-1 | 873068661 (no 0) | 0873068661
0-87306-836-X | 087306836X (correct) | 087306836X

I'm most confused firstly about how the leading zeros are incorporated into the number if starts with 978.

Your right, after I establish that its a 978 number (meaning its ISBN-13) then i can just format it as a regular number without losing data. However, I need some way to automatically check if it is indeed a 978 number and then format it appropriately.

Edit: how can i format that table i made for ez reading?

 

[Lewiy]

Try this formula:

=IF(LEFT(A1,3)="978",TEXT(SUBSTITUTE(A1,"-",""),"0000000000000"),TEXT(SUBSTITUTE(A1,"-",""),"0000000000"))

 

[ZapBrain]

wow! amazing! now i gotta check if a vlookup will work. Thnx A LOT!

 

[ZapBrain]

yep it works! Did i mention how much i love this board?!

 

[Lewiy]

No problem!!

yep it works! Did i mention how much i love this board?!

Yes I think you did

Hey all *Love this Board*

I think we all love the board