Take out zeros from code

bartoni

Active Member
Joined
Jun 10, 2003
Messages
296
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
Joined
Feb 17, 2003
Messages
8,127
I think this is it :-
=IF(MID(A1,2,1)="0",LEFT(A1,1)&RIGHT(A1,LEN(A1)-2),A1)
 

bartoni

Active Member
Joined
Jun 10, 2003
Messages
296
hmm, that didnt seem to work it just displays the contents of the cell.
 

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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
Joined
Jun 10, 2003
Messages
296
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
Joined
Sep 3, 2002
Messages
10,473
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
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top