Help - Receiving an Error Sometimes when Removing Leading Zeroes

[RHONDAK72]

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

 

[Darren Bartrup]

The problem you're having is that when you have a value with text in it the whole value is treated as text - so TEXT(O2*1,"#") will return an error as you can't multiply text by a number.

I found this works, but there may be a better way of writing it:
=IF(ISERROR(O2*1),IF(LEFT(O2,1)="0",MID(O2,2,LEN(O2)),O2),TEXT(O2,"#"))

• Is O2 * 1 an error (IF(ISERROR(O2*1))
  • TRUE - Is the first character a 0? (IF(LEFT(O2,1)="0")
    • TRUE - Take from the second character to the end of the text string. (MID(O2,2,LEN(O2)),)
    • FALSE - Return the whole string (O2)
  • ​FALSE - Return number formatted as text