Remove Leading Zeroes

[RHONDAK72]

Hello. I use Excel 2010. I have a column of cells where some values begin with leading zeroes while other values begin with a letter. Can anyone tell me how to remove only the leading zeroes?

Below is an example set of records with BEFORE and then AFTER(what I need for them to become):

BEFORE – This is the data that I pull:

PRID (column A)
000123456
000789011
000121314
N00123456

AFTER -This is what I want:

PRID (column A)
123456
789011
121314
N00123456

 

[Ferdi24]

Hi, if the leading zeroes are still in the sheet that is a clear indication that the cells are formatted as "Text".

There's a nice trick to convert your text to numbers:
- Into any cell outside your table type 1
- Copy that cell
- Select your data you want to remove the zeroes from
- Do a Paste Special (CTRL + Alt + V) and select "Multiply"
- You can now delete the cell that has the 1 in it

 

[AhoyNC]

Another option.
Note that's a zero not O between the quotes. This will leave the data as text.

Excel Workbook
	A	B
1	000123456	123456
2	000789011	789011
3	000123014	123014
4	N00123456	N00123456
Sheet

 

[RHONDAK72]

Both options work great. Thanks so much for your help!

 

[RHONDAK72]

AhoyNC, I'm actually having a problem and wonder if you can provide any insight? The values in Column O are text values that can begin with a letter or number and/or end with a letter or number. I have a loop set up so that it looks at the value under Column O and removes the leading zeroes in Column P. For Example, for O2, it is basically using this Excel formula: =IF(LEFT(O2,1)="0",TEXT(O2*1,"#"),O2). This formula turns “003” to just “3”. The leading zeroes are removed.

However, I’m running into an issue whenever the value under Column O begins with a number and ends with a letter. See the results where it shows “#VALUE!” under Column P? I need to fix this so that the proper value is returned (e.g. 06E becomes 6E, 0K7 becomes K7 and so forth…)

Col. O Col. P
003 3
091 91
021 21
085 85
068 68
105 105
086 86
06E #VALUE!
VM1 VM1
131 131
IP2 IP2
099 99
089 89
072 72
0K7 #VALUE!
098 98
0AR #VALUE!
0BN #VALUE!
0K5 #VALUE!
0BB #VALUE!
0AM #VALUE!
IP4 IP4
04U #VALUE!
0T7 #VALUE!


Below is my code:

Dim i As Integer 'Declare variable
For i = 2 To Cells(Rows.Count, "O").End(xlUp).Row
If Len(Cells(i, "O")) <> 0 Then 'If the length of the cell being analyzed is not 0 (it is populated with a value)

'then the next step is to remove leading zeroes from its existing value and place the new value in the new column you just inserted.

Cells(i, "P").Select
ActiveCell.FormulaR1C1 = _
"=IF(LEFT(RC[-1],1)=""0"",TEXT(RC[-1]*1,""#""),RC[-1])"

Else
End If
Next i

Any ideas on how to fix this to get rid of the #VALUE! error?