Take out zeros from code

[bartoni]

Hi all,

Ive got a small problem that maybe someone can help me with. Ive got a number of codes in separate cells such as:

A02B xxxxxxxxxx
A10B bbbbbbbbbb
C09X hhhhhhhhh

What id like to do is remove the zeros if there is a zero placed in the second character of the code. i.e A02B xxxxxxxxxx will become A2b xxxxxxxxxx. How would i do this.I imagine its an IF function but im not sure how to put it together to specify the second character.

Many Thanks

 

[BrianB]

I think this is it :-
=IF(MID(A1,2,1)="0",LEFT(A1,1)&RIGHT(A1,LEN(A1)-2),A1)

 

[bartoni]

hmm, that didnt seem to work it just displays the contents of the cell.

 

[BrianB]

Copy/paste your message (or this) portion A02B xxxxxxxxxx to cell A1.

Copy/paste my formula into the formula bar for cell B1 or whatever.
Get A2B xxxxxxxxxx

 

[bartoni]

i put A02B xxxxxx but its actually a descriptive sentence i just substituted it with xxxxxx for simplicity. Strangely yoo're right it works for A02b xxxxxxx but not for my actual cell content which is:


N02B NON-NARCOTIC ANALGESICS

Any ideas?

Many Thanks

 

[just_jon]

i put A02B xxxxxx but its actually a descriptive sentence i just substituted it with xxxxxx for simplicity. Strangely yoo're right it works for A02b xxxxxxx but not for my actual cell content which is:


N02B NON-NARCOTIC ANALGESICS

Any ideas?

Many Thanks

You have a space in front of N02B, making the zero the 3rd character in the string.

Maybe

=IF(MID(TRIM(A1),2,1)="0",LEFT(TRIM(A1))&MID(TRIM(A1),3,255),TRIM(A1))

EDIT to change MID ref from 2 to 3