Needing Help On How To Achieve The Following

Copper Contributor

 

How do I extract ISBN's (Book identification numbers) from Text/Character strings within a cell? 

 

ISBN's are 10 or 13 (13 in my specific case) number characters that identify a book. I will give some examples of how they are represented. 
" xxxxxxxxxxxxx by xxxxxxx  ISBN- 978xxxxxxxxxx"
"xxxxx by xxxxxx  isbn: 978-xxxxxxxxxx"
"xxxxxxxxxxxxxx - xxxxxx  isbn978xxxxxxxxx"
"978xxxxxxxxxx - by: xxxx - xxxxxxxxxxxxxxxxxxx"

So because there is no consistent cell contents the VBA I tried couldn't pick up what I was trying to accomplish. 

I was thinking or trying to Google more or less how to extract text characters to leave numbers only, but I figured I would just reach out to a community for help regarding this specific issue. 

Thanks for any help/advice! 

3 Replies

@RKD2313 Relatively easy done with Power Query (PC only, though).

Riny_van_Eekelen_0-1645971028181.png

See attached. 

Are you familiar with Power Query?

Thanks for your reply.

I Googled/YouTube how to use Power Query, but not every single row has "978" beginning ISBN's. I should've specified that, my apologies. About 85%-90% of the data does have "978" beginning ISBN's. Just unsure what or how to go about the rows that DO NOT have "978" beginning ISBN's.

If every row does not have a "978" starting ISBN, it will not let Power Query function properly. Correct?

Do I only Power Query the rows that have the "978" ISBN's?
Do I first need to delete the rows of NON "978" beginning ISBN's before entering the Power Query?

Thank you again for taking the time to help

@RKD2313 The PQ example I gave keep any number in the text. I just used your example with the 978's and added some other numbers. bit that doesn't matter for. Only numbers will be kept. Having said that, if a book title contains a number, that number will also be kept. Best to give a complete picture of your problem.