LEFT and MID

[cmac]

Hello,

I need to prep a spreadsheet to be imported into an Access table. The cells
have an ID number that consists of 10 characters. I only need to pull in 6
characters and thus am looking for the commands within Excel to allow me to
do so with some additional logic -

Numbers as they exist in the spreadsheet as is

6992065300 is in cell D4
IT5785111200 is in cell D5

Need logic (if statement?) to return the results of a single cell as follows

Cell D4 above would be the first 6 characters 699206 and retuened in the
cell where the formula resides.

Cell D5 above would need to return 578511

So, if the contents of the cells being checked does not begin with IT then
results are the first 6 characters - if the contents of the cells being
checked begin with IT then results are 6 characters beginning with the 3rd
character.

Thank you -

 

[Don Guillett]

=LEFT(SUBSTITUTE(d4,"IT",""),6)

 

[Barb Reinhardt]

How about

IF(LEFT(D4,2)="IT",MID(D4,3,6),left(D4,6))