Substitute ,replace and delete in a cell.

[Doug]

Can anyone help me with a formula ?
From the example cell contents below,i need to substitute the first
letter(A) with G,delete the the third number (0),and delete the last three
numbers(000)

All the cells containing my data are in exactly the same sequence,so the
formula would be the same for all.

AB02030000

Thanks in anticipation.

Doug

[Ron Coderre]

Would this work for you?:

For A1: AB02030000

B1: ="G"&MID(A1,2,3)&MID(A1,6,2)
Returns GB0230


••••••••••
Regards,
Ron


"Doug" wrote:

> Can anyone help me with a formula ?
> From the example cell contents below,i need to substitute the first
> letter(A) with G,delete the the third number (0),and delete the last three
> numbers(000)
>
> All the cells containing my data are in exactly the same sequence,so the
> formula would be the same for all.
>
> AB02030000
>
> Thanks in anticipation.
>
> Doug
>

[Doug]

Thanks Ron,

I'm looking to return "GB2300" (deleting the first zero),but you have given
me enough to work it out myself.

Many Thanks

Doug


"Ron Coderre" wrote:

> Would this work for you?:
>
> For A1: AB02030000
>
> B1: ="G"&MID(A1,2,3)&MID(A1,6,2)
> Returns GB0230
>
>
> ••••••••••
> Regards,
> Ron
>
>
> "Doug" wrote:
>
> > Can anyone help me with a formula ?
> > From the example cell contents below,i need to substitute the first
> > letter(A) with G,delete the the third number (0),and delete the last three
> > numbers(000)
> >
> > All the cells containing my data are in exactly the same sequence,so the
> > formula would be the same for all.
> >
> > AB02030000
> >
> > Thanks in anticipation.
> >
> > Doug
> >