LEFT and MID
 

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 -

LEFT and MID
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"cmac" wrote in message
...
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 -

LEFT and MID
 

How about

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

--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"cmac" wrote:

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 -