Remove 4 and 7 character from a cell

[excelbag92]

I am trying to change grid references from 8 characters to 6. Can you help me workout how to remove the 4th and 7th character using a formula so that I can drag the formula down the whole column please?

so I want SK883070 to become SK8807

I have tried nested replace but this just adds additional I.E SK883070 becomes SK8 83070

 

[Fluff]

Hi & welcome to MrExcel.
Your description & expect result don't match. Which is correct?

 

[excelbag92]

Apoligies, it's the 4th and 8th character that I wish to remove. I did look to change the thread title but couldn't workout how.

 

[Fluff]

Ok, how about

=REPLACE(LEFT(A2,7),4,1,"")

 

[excelbag92]

Ok, how about

=REPLACE(LEFT(A2,7),4,1,"")

Yes, that worked only the 4 needed changing to a 5.

What does the 7 represent btw? I don't quite understand that because the 7th character was actually a 7 which remains.

Thank you very much

 

[Fluff]

The 7 means take the 1st 7 characters.

 

[excelbag92]

Thanks I understand that now.

 

[Fluff]

Glad to help & thanks for the feedback.