plz help me

[soumyajit]

I have a database in A1 looking like this

085650777

656464

08356343

08354350854

655557

083453454

544408474

545640875

08433333

343345

08450844

as we can see some numbers contains 08 as first two digits.I just wanted to know is there any formula which eliminate or separate those 08(first two digits) from those numbers.Is there any one how can help me plz.

 

[Jai9]

Hi,


Try


=IF(LEFT(A1,2)=08,REPLACE(A1,1,2,""),A1)


Jai

 

[Dhamo]

Try this.


Assuming your data are in column A and from row 1.


B1=IF(MID(A1,1,2)<>"08",A1,MID(A1,3,100))

 

[Dhamo]

Jai's formula will also work with a slight modification.


=IF(LEFT(A1,2)="08",REPLACE(A1,1,2,""),A1)

 

[Faseeh]

@ soumyajit,


Assuming your data in A1:A11, use this with ctrl+shift+enter:

=IFERROR(INDEX($A$1:$A$11,SMALL((IF(LEFT(TEXT($A$1:$A$11,"@"),1)="8",ROW($A$1:$A$11))),ROW(A1)),0),"")

and drag down.


Faseeh