extract and convert to a number to be able to match

[Korimint]

Hello,

I have 2 different numbers, the first one is this:

%B710743102091803^?;71074

I want to convert it to this:

710743102091803

and make it an actual number that excel will recognize to be able to match the number in a different column.

The other number I have is this:

;6050110000002353239=2501

I want this:

6050110000002353239

and then convert to a number to match also.

I have tried using the MID and then adding 0 but it only will do up to 14 characters which doesnt work. Any ideas on what to do?

Thanks for the help!

I am using excel 2007 and windows 7

 

[stilesbn]

Just left and right will do it assuming that if you have other numbers they are similar to these.

Putting the first number in cell A1 use this formula:

=LEFT(RIGHT(A1,23),15)

and the second number in cell A2:

=LEFT(RIGHT(A2,24),19)

 

[Korimint]

Just left and right will do it assuming that if you have other numbers they are similar to these.

Putting the first number in cell A1 use this formula:

=LEFT(RIGHT(A1,23),15)

and the second number in cell A2:

=LEFT(RIGHT(A2,24),19)

Yes this gets me the number but not one that excel recognizes to be able to match the numbers, is there a way to make a number text, so it can see both and match them up?

 

[Just Jan]

Excel only recognises numbers up to 15 digits, any longer are displayed as scientific and truncated. Leave them as text and you can still match them =A1=B1 or =EXACT(A1,B!)

To convert to text use =TEXT(A1,"#")