Replacing characters with leading zeroes
I am attempting to replace the character "O" with a
leading zero in a column. The column currently looks something like this: OOO55 OOO3 OOOOO567 OO1 I would like it to look like: 00055 0003 00000567 001 I have tried formatting the column as text and then doing a find and replace. However, as soon as the replace finishes the column format switches back to a number format and strips the zeroes. Unfortunately, I can't use a custom number format because there is no consistency to the length of the numbers. Any ideas? |
Replacing characters with leading zeroes
On Tue, 27 Jul 2004 17:51:50 -0700, "Demian Valle"
wrote: I am attempting to replace the character "O" with a leading zero in a column. The column currently looks something like this: OOO55 OOO3 OOOOO567 OO1 I would like it to look like: 00055 0003 00000567 001 I have tried formatting the column as text and then doing a find and replace. However, as soon as the replace finishes the column format switches back to a number format and strips the zeroes. Unfortunately, I can't use a custom number format because there is no consistency to the length of the numbers. Any ideas? =TEXT(SUBSTITUTE(A1,"O",""),REPT("0",LEN(A1))) --ron |
Replacing characters with leading zeroes
On Tue, 27 Jul 2004 17:51:50 -0700, "Demian Valle"
wrote: I am attempting to replace the character "O" with a leading zero in a column. The column currently looks something like this: OOO55 OOO3 OOOOO567 OO1 I would like it to look like: 00055 0003 00000567 001 I have tried formatting the column as text and then doing a find and replace. However, as soon as the replace finishes the column format switches back to a number format and strips the zeroes. Unfortunately, I can't use a custom number format because there is no consistency to the length of the numbers. Any ideas? Another thought. If you want to change the values in place, and have them come out as a formatted number, you could use this UDF: =================================== Sub foo() Dim c As Range Dim NumZeros As Integer For Each c In Selection NumZeros = Len(Trim(c.Text)) c.Value = Replace(c.Text, "O", "") c.NumberFormat = Application.WorksheetFunction.Rept("0", NumZeros) Next c End Sub ================================= --ron |
Replacing characters with leading zeroes
Thanks Ron. This is exactly what I needed.
Demian -----Original Message----- On Tue, 27 Jul 2004 17:51:50 -0700, "Demian Valle" wrote: I am attempting to replace the character "O" with a leading zero in a column. The column currently looks something like this: OOO55 OOO3 OOOOO567 OO1 I would like it to look like: 00055 0003 00000567 001 I have tried formatting the column as text and then doing a find and replace. However, as soon as the replace finishes the column format switches back to a number format and strips the zeroes. Unfortunately, I can't use a custom number format because there is no consistency to the length of the numbers. Any ideas? Another thought. If you want to change the values in place, and have them come out as a formatted number, you could use this UDF: =================================== Sub foo() Dim c As Range Dim NumZeros As Integer For Each c In Selection NumZeros = Len(Trim(c.Text)) c.Value = Replace(c.Text, "O", "") c.NumberFormat = Application.WorksheetFunction.Rept ("0", NumZeros) Next c End Sub ================================= --ron . |
Replacing characters with leading zeroes
On Wed, 28 Jul 2004 08:53:27 -0700, "Demian Valle"
wrote: Thanks Ron. This is exactly what I needed. Demian You're welcome. Glad to help. --ron |
All times are GMT +1. The time now is 03:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com