# Take out zeros from code

#### bartoni

##### Active Member
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

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### BrianB

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

#### bartoni

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

#### BrianB

##### Well-known Member
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

##### Active Member
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

##### Legend
bartoni said:
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

Replies
2
Views
277
Replies
2
Views
418
Replies
4
Views
820
Replies
5
Views
416
Replies
1
Views
439