Remove leading 0 before decimal except when another digit precedes the 0.

[4EverMaAT]

I have a column, which has a comma separated values inside each cell that look like this

0.1, 0.2,0.3, 0.4,0.5, 0.8,1.0 
1.5, 1.6,2.0, 10.6,10.9, 15.2,30.75 
20, 0.25,280.2, 0.29,300.2, 423,530.76

In a text string.
The goal is to remove the leading zero<code style="font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; margin: 0px; padding: 1px 5px; border: 0px; border-image-source: initial; border-image-slice: initial; border-image-width: initial; border-image-outset: initial; border-image-repeat: initial; white-space: pre-wrap; background-color: rgb(239, 240, 241);">0</code> in front of the decimal<code style="font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; margin: 0px; padding: 1px 5px; border: 0px; border-image-source: initial; border-image-slice: initial; border-image-width: initial; border-image-outset: initial; border-image-repeat: initial; white-space: pre-wrap; background-color: rgb(239, 240, 241);">.</code>, but only when there is no other digit (including another 0) in front of it. I use the search replace function vba:

Option Explicit
    Public Sub Replace0dot(Optional byDummy As Byte)
        Columns("A").Replace [B]What:"0.", _
                            Replacement:=".", _
                            [/B]LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            MatchCase:=False, _
                            SearchFormat:=False, _
                            ReplaceFormat:=False
    Application.ScreenUpdating = True
    End Sub

to remove <code style="font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; margin: 0px; padding: 1px 5px; border: 0px; border-image-source: initial; border-image-slice: initial; border-image-width: initial; border-image-outset: initial; border-image-repeat: initial; white-space: pre-wrap; background-color: rgb(239, 240, 241);">0.</code> and replace with only<code style="font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; margin: 0px; padding: 1px 5px; border: 0px; border-image-source: initial; border-image-slice: initial; border-image-width: initial; border-image-outset: initial; border-image-repeat: initial; white-space: pre-wrap; background-color: rgb(239, 240, 241);">.</code> and I end up with this:

.1, .2,.3, .4,.5, .8,1
1.5, 1.6,2, 1.6,1.9, 15.2,3.75
2, .25,28.2, .29,30.2, 423,53.76

It removes all instances of leading 0. with <code style="font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; margin: 0px; padding: 1px 5px; border: 0px; border-image-source: initial; border-image-slice: initial; border-image-width: initial; border-image-outset: initial; border-image-repeat: initial; font-size: 13px; white-space: pre-wrap; background-color: rgb(239, 240, 241);">.</code>, so you see <code style="font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; margin: 0px; padding: 1px 5px; border: 0px; border-image-source: initial; border-image-slice: initial; border-image-width: initial; border-image-outset: initial; border-image-repeat: initial; font-size: 13px; white-space: pre-wrap; background-color: rgb(239, 240, 241);">10.6</code> becomes <code style="font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; margin: 0px; padding: 1px 5px; border: 0px; border-image-source: initial; border-image-slice: initial; border-image-width: initial; border-image-outset: initial; border-image-repeat: initial; font-size: 13px; white-space: pre-wrap; background-color: rgb(239, 240, 241);">1.6</code>. But it should remain <code style="font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; margin: 0px; padding: 1px 5px; border: 0px; border-image-source: initial; border-image-slice: initial; border-image-width: initial; border-image-outset: initial; border-image-repeat: initial; font-size: 13px; white-space: pre-wrap; background-color: rgb(239, 240, 241);">10.6</code>

How can I get a search replace equivalent that gives me:

.1, .2,.3, .4,.5, .8,1
1.5, 1.6,2, 10.6,10.9, 15.2,30.75
20, .25,280.2, .29,300.2, 423,530.76

Any ideas? I'm typically going to have an 20+ rows of column A with each cell having a text string as stated above. This column is actually generated from another script that combines the numbers, commas, and spaces in a specific way to make the string. The cells were originally custom formatted <code style="font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; margin: 0px; padding: 1px 5px; border: 0px; border-image-source: initial; border-image-slice: initial; border-image-width: initial; border-image-outset: initial; border-image-repeat: initial; white-space: pre-wrap; background-color: rgb(239, 240, 241);">.##</code>, which leaves off the leading 0 when shown in excel. But the formula bar still remembers <code style="font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; margin: 0px; padding: 1px 5px; border: 0px; border-image-source: initial; border-image-slice: initial; border-image-width: initial; border-image-outset: initial; border-image-repeat: initial; white-space: pre-wrap; background-color: rgb(239, 240, 241);">0.##</code>

 

[István Hirsch]

Try this: What:=" 0.", Replacement:=" ." Also, you should put a space at the start of each cell, to recognize the search string if it is in start position.

 

[4EverMaAT]

Try this: What:=" 0.", Replacement:=" ." Also, you should put a space at the start of each cell, to recognize the search string if it is in start position.

This is not a bonafide solution for 2 reasons:
1) That would require that I go to each cell and delete the 0 in the first 0.xxx . I may have 100+ rows of these strings.
2) Also, what about situations like [FONT=Consolas, Menlo, Monaco, Lucida Console, Liberation Mono, DejaVu Sans Mono, Bitstream Vera Sans Mono, Courier New, monospace, sans-serif] 0.43,0.56,0.67 [/FONT]? You are left with .43,0.56,0.67 ; not every value in the string has a "<code style="margin: 0px; padding: 1px 5px; font-style: inherit; font-weight: inherit; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; line-height: 13px; border: 0px; border-image-source: initial; border-image-slice: initial; border-image-width: initial; border-image-outset: initial; border-image-repeat: initial; white-space: pre-wrap; background-color: rgb(239, 240, 241);"> </code>" in front of it.

 

[István Hirsch]

Do you want to convert 1.0 to 1 and 2.0 to 2?

 

[Jonmo1]

Assuming it's acceptable (if not desireable) for this 10.6,10.9 to change to 10.6, 10.9
i.e. All values should be seperated by comma space

Here's a formula method. I'll leave it to you to translate into VBA if it really must be done with VBA

=TRIM(SUBSTITUTE(" "&SUBSTITUTE(A1,",",", ")," 0."," ."))

 

[4EverMaAT]

Do you want to convert 1.0 to 1 and 2.0 to 2?

no. the OP makes no mention of this. Fortunately the answer was provided on superuser

[COLOR=#4A33DA][FONT=Consolas]Sub[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] fixdata2[/FONT][/COLOR][COLOR=#0C0D0E][FONT=Consolas]()[/FONT][/COLOR]<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">[COLOR=#4A33DA]Dim[/COLOR][COLOR=#303336] r [/COLOR][COLOR=#4A33DA]As[/COLOR][COLOR=#303336] Range[/COLOR][COLOR=#0C0D0E],[/COLOR][COLOR=#303336] t [/COLOR][COLOR=#4A33DA]As[/COLOR][COLOR=#4A33DA]String[/COLOR][COLOR=#303336]

    [/COLOR][COLOR=#4A33DA]For[/COLOR][COLOR=#4A33DA]Each[/COLOR][COLOR=#303336] r [/COLOR][COLOR=#4A33DA]In[/COLOR][COLOR=#303336] Intersect[/COLOR][COLOR=#0C0D0E]([/COLOR][COLOR=#303336]Range[/COLOR][COLOR=#0C0D0E]([/COLOR][COLOR=#008B31]"A:A"[/COLOR][COLOR=#0C0D0E]),[/COLOR][COLOR=#303336] ActiveSheet[/COLOR][COLOR=#0C0D0E].[/COLOR][COLOR=#303336]UsedRange[/COLOR][COLOR=#0C0D0E])[/COLOR][COLOR=#303336]
        t [/COLOR][COLOR=#0C0D0E]=[/COLOR][COLOR=#303336] r[/COLOR][COLOR=#0C0D0E].[/COLOR][COLOR=#303336]Text
        [/COLOR][COLOR=#4A33DA]If[/COLOR][COLOR=#303336] Left[/COLOR][COLOR=#0C0D0E]([/COLOR][COLOR=#303336]t[/COLOR][COLOR=#0C0D0E],[/COLOR][COLOR=#B00633]2[/COLOR][COLOR=#0C0D0E])[/COLOR][COLOR=#0C0D0E]=[/COLOR][COLOR=#008B31]"0."[/COLOR][COLOR=#4A33DA]Then[/COLOR][COLOR=#303336] t [/COLOR][COLOR=#0C0D0E]=[/COLOR][COLOR=#303336] Mid[/COLOR][COLOR=#0C0D0E]([/COLOR][COLOR=#303336]t[/COLOR][COLOR=#0C0D0E],[/COLOR][COLOR=#B00633]2[/COLOR][COLOR=#0C0D0E])[/COLOR][COLOR=#303336]
        t [/COLOR][COLOR=#0C0D0E]=[/COLOR][COLOR=#303336] Replace[/COLOR][COLOR=#0C0D0E]([/COLOR][COLOR=#303336]t[/COLOR][COLOR=#0C0D0E],[/COLOR][COLOR=#008B31]" 0."[/COLOR][COLOR=#0C0D0E],[/COLOR][COLOR=#008B31]" ."[/COLOR][COLOR=#0C0D0E])[/COLOR][COLOR=#303336]
        t [/COLOR][COLOR=#0C0D0E]=[/COLOR][COLOR=#303336] Replace[/COLOR][COLOR=#0C0D0E]([/COLOR][COLOR=#303336]t[/COLOR][COLOR=#0C0D0E],[/COLOR][COLOR=#008B31]",0."[/COLOR][COLOR=#0C0D0E],[/COLOR][COLOR=#008B31]",."[/COLOR][COLOR=#0C0D0E])[/COLOR][COLOR=#303336]
        r[/COLOR][COLOR=#0C0D0E].[/COLOR][COLOR=#303336]Value [/COLOR][COLOR=#0C0D0E]=[/COLOR][COLOR=#303336] t
    [/COLOR][COLOR=#4A33DA]Next[/COLOR][COLOR=#303336] r [/COLOR]</code>[COLOR=#4A33DA][FONT=Consolas]End[/FONT][/COLOR][COLOR=#4A33DA][FONT=Consolas]Sub[/FONT][/COLOR]

It does exactly what was requested from OP, which was to remove leading 0s, but only from comma-separated values in the string that had more than 1 digit, and had decimal afterwards, and did not have a digit in front of the zero.

So a string in each cell of a like this:

0.1, 0.2,0.3, 0.4,0.5, 0.8,10.6

becomes

.1, .2,.3, .4,.5, .8,10.6

All the other original string formatting is preserved.