Extract number from a alphanumerica string

[Himanshu]

Hi Everyone,


Cell A1 contains 00I6600000715 3/1/2010 i need to have in Cell B1 Just 16600000715.

Need to remove first two "zeros", convert "I" into number 1 and finally removing date from the string leaving only required data that is 16600000715.


Any formula or macro to resolve the above puzzle would be a great help. Thanks in advance.

 

[Hui]

Try

=VALUE(SUBSTITUTE(LEFT(A1,FIND(" ",A1)),"I","1"))

 

[Himanshu]

Wow,It works.

Thanks Excel Ninja

 

[Himanshu]

Cell A1 contains 00I6600000715 3/1/2010 i need to have in Cell B1 Just 16600000715.

Solution -- =VALUE(SUBSTITUTE(LEFT(A1,FIND(" ",A1)),"I","1")) works fine!


In addition to above scenario, I just figured out that there is a variation in Cell A1 That contains 00I6600000715 3/1/2010 sometimes this data doesn't have "I" there,so if data contains "I" replace it with "1" if not leave the data as it is.


Data Desired Result

00I6600000715 3/1/2010 16600000715

0016600000715 3/1/2010 16600000715


Thanks

 

[Hui]

Try:

=IF(MID(A1,3,1)="I",VALUE(SUBSTITUTE(LEFT(A1,FIND(" ",A1)),"I","1")),VALUE(LEFT(A1,FIND(" ",A1))))