Needing Help On How To Achieve The Following

%3CLINGO-SUB%20id%3D%22lingo-sub-3218115%22%20slang%3D%22en-US%22%3ENeeding%20Help%20On%20How%20To%20Achieve%20The%20Following%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3218115%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20I%20extract%20ISBN's%20(Book%20identification%20numbers)%20from%20Text%2FCharacter%20strings%20within%20a%20cell%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EISBN's%20are%2010%20or%2013%20(13%20in%20my%20specific%20case)%20number%20characters%20that%20identify%20a%20book.%20I%20will%20give%20some%20examples%20of%20how%20they%20are%20represented.%26nbsp%3B%3CBR%20%2F%3E%22%20xxxxxxxxxxxxx%20by%20xxxxxxx%26nbsp%3B%20ISBN-%20978xxxxxxxxxx%22%3CBR%20%2F%3E%22xxxxx%20by%20xxxxxx%26nbsp%3B%20isbn%3A%20978-xxxxxxxxxx%22%3CBR%20%2F%3E%22xxxxxxxxxxxxxx%20-%20xxxxxx%26nbsp%3B%20isbn978xxxxxxxxx%22%3CBR%20%2F%3E%22978xxxxxxxxxx%20-%20by%3A%20xxxx%20-%20xxxxxxxxxxxxxxxxxxx%22%3CBR%20%2F%3E%3CBR%20%2F%3ESo%20because%20there%20is%20no%20consistent%20cell%20contents%20the%20VBA%20I%20tried%20couldn't%20pick%20up%20what%20I%20was%20trying%20to%20accomplish.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EI%20was%20thinking%20or%20trying%20to%20Google%20more%20or%20less%20how%20to%20extract%20text%20characters%20to%20leave%20numbers%20only%2C%20but%20I%20figured%20I%20would%20just%20reach%20out%20to%20a%20community%20for%20help%20regarding%20this%20specific%20issue.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20for%20any%20help%2Fadvice!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3218115%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3218159%22%20slang%3D%22en-US%22%3ERe%3A%20Needing%20Help%20On%20How%20To%20Achieve%20The%20Following%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3218159%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1319002%22%20target%3D%22_blank%22%3E%40RKD2313%3C%2FA%3E%26nbsp%3BRelatively%20easy%20done%20with%20Power%20Query%20(PC%20only%2C%20though).%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Riny_van_Eekelen_0-1645971028181.png%22%20style%3D%22width%3A%20508px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F351510i4BD8CFBC058639C2%2Fimage-dimensions%2F508x112%3Fv%3Dv2%22%20width%3D%22508%22%20height%3D%22112%22%20role%3D%22button%22%20title%3D%22Riny_van_Eekelen_0-1645971028181.png%22%20alt%3D%22Riny_van_Eekelen_0-1645971028181.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ESee%20attached.%26nbsp%3B%3C%2FP%3E%3CP%3EAre%20you%20familiar%20with%20Power%20Query%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3218511%22%20slang%3D%22en-US%22%3ERe%3A%20Needing%20Help%20On%20How%20To%20Achieve%20The%20Following%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3218511%22%20slang%3D%22en-US%22%3EThanks%20for%20your%20reply.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20Googled%2FYouTube%20how%20to%20use%20Power%20Query%2C%20but%20not%20every%20single%20row%20has%20%22978%22%20beginning%20ISBN's.%20I%20should've%20specified%20that%2C%20my%20apologies.%20About%2085%25-90%25%20of%20the%20data%20does%20have%20%22978%22%20beginning%20ISBN's.%20Just%20unsure%20what%20or%20how%20to%20go%20about%20the%20rows%20that%20DO%20NOT%20have%20%22978%22%20beginning%20ISBN's.%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20every%20row%20does%20not%20have%20a%20%22978%22%20starting%20ISBN%2C%20it%20will%20not%20let%20Power%20Query%20function%20properly.%20Correct%3F%3CBR%20%2F%3E%3CBR%20%2F%3EDo%20I%20only%20Power%20Query%20the%20rows%20that%20have%20the%20%22978%22%20ISBN's%3F%3CBR%20%2F%3EDo%20I%20first%20need%20to%20delete%20the%20rows%20of%20NON%20%22978%22%20beginning%20ISBN's%20before%20entering%20the%20Power%20Query%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20again%20for%20taking%20the%20time%20to%20help%3C%2FLINGO-BODY%3E
New 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.