Trying to remove everything but the numbers from 446818 rows

[BirbinaRoadster]

Hi there,

I'm confident with using Access but Excel is very new and confusing to me. I have been given 446818 ISBN numbers in a text file, some are 13 numbers long and some are 10 long. Where the information was pulled, some of the numbers had text after them e.g: (paperback). When the text file was created, it was done so that the 13 digit numbers worked fine but some of the short ones now have all sorts of random stuff showing e.g: (p, (c, |, :, X and some rows have random bits of text on them. I need these numbers to be clean so that we can put them into another database.

I've been searching all morning on google for anything that can help but I haven't had any success. Surely there is some simple function that can do this or maybe my expectations of excel are way too high I hope someone can help!

Thank you so much!

 

[Trebor76]

Hi BirbinaRoadster,

Welcome to MrExcel!!

Assuming there's a space after the ISBN number that's followed by the text and the first cell is A2, you can put this formula into an empty cell...

=VALUE(LEFT(A2,SEARCH(" ",A2)-1))

...and copy it down as far as required.

HTH

Robert

 

[Rick Rothstein]

Hi there,

I'm confident with using Access but Excel is very new and confusing to me. I have been given 446818 ISBN numbers in a text file, some are 13 numbers long and some are 10 long. Where the information was pulled, some of the numbers had text after them e.g: (paperback). When the text file was created, it was done so that the 13 digit numbers worked fine but some of the short ones now have all sorts of random stuff showing e.g: (p, (c, |, :, X and some rows have random bits of text on them. I need these numbers to be clean so that we can put them into another database.

What is your ultimate result goal for the text file... to create a "clean" text file or to put clean information into an Excel worksheet?

 

[BirbinaRoadster]

The goal is to have a 'clean' text file, I've been tasked with finding a way to clean it to send off to someone else

 

[BirbinaRoadster]

Assuming there's a space after the ISBN number that's followed by the text and the first cell is A2, you can put this formula into an empty cell...

=VALUE(LEFT(A2,SEARCH(" ",A2)-1))

Thank you for trying, this worked for some but not all

 

[Trebor76]

Thank you for trying, this worked for some but not all

Can you post some examples where it didn't work? Is there a pattern where it's not working i.e. for ISBN numbers that are 10 digits or when they're 13 digits?

As long as every entry starts with an ISBN, see if this formula is better:

=IF(ISNUMBER(VALUE(LEFT(A2,13))),VALUE(LEFT(A2,13)),VALUE(LEFT(A2,10)))

 

[jtakw]

Hi,

If the data/numbers you want extracted is Always at the beginning of the string and up to 13 digits, this should work:

Book1
	A	B
1	1234567890 asdf	1234567890
2	1234567890asdf	1234567890
3	1234567890123 asdf	1234567890123
4	1234567890123asdf	1234567890123
5	9999999999999 asdf	9999999999999
6	9999999999999asdf	9999999999999
Sheet6

Cell Formulas
Range	Formula
B1	=LOOKUP(10^13,0+LEFT(A1,ROW($1:$13)))

Formula copied down.

 

[jtakw]

Another formula to consider if your numbers can Only by either 13 or 10 digits.
B1 formula will extract anywhere from 1 to 13 digits, C1 formula will only be accurate for 13 or 10 digits.

Book1
	A	B	C
1	1234567890 asdfg	1234567890	1234567890
2	1234567890asdf	1234567890	1234567890
3	1234567890123 asdf	1234567890123	1234567890123
4	1234567890123asdfg	1234567890123	1234567890123
5	9999999999999 asdf	9999999999999	9999999999999
6	9999999999999asdf	9999999999999	9999999999999
Sheet6

Cell Formulas
Range	Formula
B1	=LOOKUP(10^13,0+LEFT(A1,ROW($1:$13)))
C1	=LEFT(A1,IF(ISNUMBER(LEFT(A1,13)+0),13,10))+0

 

[BirbinaRoadster]

Thanks for all your help guys! I've had a bit of a play around with your suggestions and have managed to clean it up pretty well now

 

[jtakw]

You're welcome, welcome to the forum.