Remove leading zero within a text field

bflanagan

New Member
Joined
Dec 8, 2015
Messages
10
Hello,

I have a list of codes I need to removed the leading zero from. However, I only want to remove the leading zero and not all of them.

I have been looking for a while but cant seem to figure it out. Any ideas?

The codes look like this:

AB01
AB02
AB03
AB05
AB10

<tbody>
</tbody>
and I need them to look like this:

AB1
AB2
AB3
AB5
AB10


Thanks!

<tbody>
</tbody>
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Welcome to the board.

Try this assuming the first part is always 2 characters before the numbers.

=LEFT(A1,2)&RIGHT(A1,LEN(A1)-2)+0
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
3,918
Office Version
365, 2019, 2016
Platform
Windows
try if this little trick works for you or not

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">AB01</td><td style="text-align: right;;"></td><td style=";">AB1</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">AB02</td><td style="text-align: right;;"></td><td style=";">AB2</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">AB03</td><td style="text-align: right;;"></td><td style=";">AB3</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">AB05</td><td style="text-align: right;;"></td><td style=";">AB5</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">AB10</td><td style="text-align: right;;"></td><td style=";">AB10</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C1</th><td style="text-align:left">=IF(<font color="Blue">FIND(<font color="Red">0,A1</font>)<LEN(<font color="Red">A1</font>),SUBSTITUTE(<font color="Red">A1,0,""</font>),A1</font>)</td></tr></tbody></table></td></tr></table><br />
 

bflanagan

New Member
Joined
Dec 8, 2015
Messages
10
Thanks Jonmo1, that worked really well!

Only problem is a few of my codes only have 1 character before the leading zero. Is there a way to take this into account?

Thanks
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Are there any other variations?
Is it always either 1 or 2 characters before the first number, never 3 or more?
 

bflanagan

New Member
Joined
Dec 8, 2015
Messages
10
There are also sometimes 3 characters before yes.

Here are the different types of codes:


A01
AB01
ABC01
A01B

Also, some codes are already in the correct format e.g.

A1
AB1
ABC1
A1B
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Try

=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&15^12))-1)&RIGHT(A1,LEN(A1)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&15^12))+1)+0
 

bflanagan

New Member
Joined
Dec 8, 2015
Messages
10
Thanks AlanY!

This worked really well! However any data that was already correct (i.e. without the leading zero) fails.

E.g.

AB01AB1
AB02AB2
AB03AB3
AB05AB5
AB10AB10
AB11#VALUE!
AB12#VALUE!
AB13#VALUE!
AB14#VALUE!
AB15#VALUE!



<colgroup><col><col></colgroup><tbody>
</tbody>
 

bflanagan

New Member
Joined
Dec 8, 2015
Messages
10
Works really well! Thanks.

It fails on codes where there is a letter after the code (i.e. AB1C), however as there aren't many of these I can just manually update them. Thanks!!
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
3,918
Office Version
365, 2019, 2016
Platform
Windows
Thanks AlanY!

This worked really well! However any data that was already correct (i.e. without the leading zero) fails.

E.g.

AB01AB1
AB02AB2
AB03AB3
AB05AB5
AB10AB10
AB11#VALUE!
AB12#VALUE!
AB13#VALUE!
AB14#VALUE!
AB15#VALUE!



<colgroup><col><col></colgroup><tbody>
</tbody>
how about

<b>Excel 2012</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">AB01</td><td style=";">AB1</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">AB02</td><td style=";">AB2</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">AB03</td><td style=";">AB3</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">AB05</td><td style=";">AB5</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">AB10</td><td style=";">AB10</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">AB11</td><td style=";">AB11</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">AB12</td><td style=";">AB12</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">AB13</td><td style=";">AB13</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">AB14</td><td style=";">AB14</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">AB15</td><td style=";">AB15</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">=IFERROR(<font color="Blue">IF(<font color="Red">FIND(<font color="Green">0,A1</font>)<LEN(<font color="Green">A1</font>),SUBSTITUTE(<font color="Green">A1,0,""</font>),A1</font>),A1</font>)</td></tr></tbody></table></td></tr></table><br />
 

Forum statistics

Threads
1,081,415
Messages
5,358,533
Members
400,502
Latest member
price83

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top