Need Help. Remove Leading Zeroes from Text Cells

[RHONDAK72]

I use Excel 2010. I’m trying to figure out a way to remove the leading zeroes from a text field. I want to eventually code the process in VBA.

The text field could be different number/text combinations such as the examples below. In the end, I want the following results:

01234500 would become 1234500
001234CD would become 1234CD
000123EF would become 123EF
123456CD would become 123456CD
AB012345 would become AB012345
AB0123CD would become AB0123CD
AB123456 would become AB123456

I’ve tried 2 different options and neither option below works, because it either ignores the leading zeroes or it returns an error. See the examples below.

OPTION 1: Multiply by 1 Method:

1. Enter 1 in a cell and copy it.
2. Select the cells that you wish to alter.
3. Right click and select Paste Special.
4. Choose multiply.
5. Click OK.

Results using this method:
01234500 becomes 1234500
001234CD becomes 001234CD – Leading zeroes remain. This won’t work.
123456CD becomes 123456CD
000123EF becomes 000123EF – Leading zeroes remain. This won’t work.
AB012345 becomes AB012345
AB0123CD becomes AB0123CD
AB123456 becomes AB123456

OPTION 2:

Example Formula:
=IF(LEFT(A2,1)="0",TEXT(A2*1,"#"),A2)

Results using this method:

01234500 becomes 1234500
001234CD becomes #VALUE! – This error won’t work.
123456CD becomes 123456CD
000123EF becomes #VALUE! – This error won’t work.
AB012345 becomes AB012345
AB0123CD becomes AB0123CD
AB123456 becomes AB123456

Does anyone have any ideas?

 

[Blade Hunter]

This UDF works:

Function CleanZero(MyText As String)
    CleanZero = Replace(LTrim(Replace(MyText, "0", " ")), " ", 0)
End Function

 

[AhoyNC]

Another option:

Excel Workbook
	A	B
1	1234500	1234500
2	001234CD	1234CD
3	000123EF	123EF
4	123456CD	123456CD
5	AB012345	AB012345
6	AB0123CD	AB0123CD
7	AB123456	AB123456
Sheet