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
 

Some videos you may like

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

Watch MrExcel Video

Forum statistics

Threads
1,118,291
Messages
5,571,331
Members
412,382
Latest member
Langtn02
Top