Treating letters in a string as numbers

[rogue.phoenix]

Hi all, this seems like it should be a simple thing, but I am stumped. I am entering ISBN numbers from my book collection, and some of them end with a letter (Generally "X"). This is making it near impossible to set to the standard ISBN-13 format (000-0-00000-000-0). For example, what I have is "156504018x". What I need to do to it is format it to look like "000-1-56504-018-X". I set up the format to be 000-0-00000-000-0, and it works great until it gets to the ones that involve the letter. Can someone please help?!?!?!

[FDibbins]

Hi, welcome to the forum

If you would be willing to remove the x, that could be done (probably) with a helper column?

[Ghozi Alkatiri]

try this formula
=TEXT(LEFT(A1;LEN(A1)-1);"0-000000-00000")&RIGHT(A1)

[rogue.phoenix]

The problem is that I am transferring the information into a program to search the internet to find the books. The X is unfortunately a necessary evil. You did give me an idea however... Might be able to break the whole ISBN down to the separate specific parts then re-concatenate them... That sure seems like a lot of work for something that seems so simple though...

[rogue.phoenix]

try this formula
=TEXT(LEFT(A1;LEN(A1)-1);"0-000000-00000")&RIGHT(A1)

Once I replaced all of the ";" with "," and made a small modification to the format, that worked like a charm! Here's the updated format in case anyone else has a similar problem.

=TEXT(LEFT(A1,LEN(A1)-1),"000-0-00000-000-")&RIGHT(A1)

[FDibbins]

Well even if you format the contents to look like that, that will not affect the underlying cell contents. Formatting a a cosmetic change so things look how we want them to look.

A cell could display 000-1-56504-018-X, but the actual cell contents would still just be 000156504018X

[ianpage]

rogue-ph, you could simplify that a bit given that the input is always exactly 13 characters. In that case there's no need to to use the LEN(A1-1) part. So your formula would be =TEXT(LEFT(A1,12),"000-0-00000-000-")&RIGHT(A1)

FDibbins is right that a number that is formatted for display using Excel's number formats will not work as a search term for your purposes. However, the formula we're discussing produces a text output (not a number formatted for display), so you'll be just fine using it as a search term.

[Richard Buttrey]

Here's a UDF that should work

Enter =ISBN(A1)

Please Login or Register  to view this content.

[FDibbins]

rogue-ph, you could simplify that a bit given that the input is always exactly 13 characters. In that case there's no need to to use the LEN(A1-1) part. So your formula would be =TEXT(LEFT(A1,12),"000-0-00000-000-")&RIGHT(A1)

FDibbins is right that a number that is formatted for display using Excel's number formats will not work as a search term for your purposes. However, the formula we're discussing produces a text output (not a number formatted for display), so you'll be just fine using it as a search term.

Ian, I missed your post after posting mine. yes, your should work for a search, because it is actually changing the cell contents