# 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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,057
Messages
5,835,163
Members
430,343
Latest member
t0m_c

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back