Substitute ,replace and delete in a cell.

G

Guest

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
 
G

Guest

Would this work for you?:

For A1: AB02030000

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


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

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to substitute whole word with different designated letters? 4
To remove numbers in a cell with out using the macro 3
Substitute function yields #VALUE! when add the instance_number 7
Substitute for Dragging 4
Trim and Substitute not working 4
SUBSTITUTE formula -- variable spacing between parameters?? 3
Substitute letters by others in a word 4
sum numbers in cell string array 6

Top