Replace certain commas in a string

[hgufrin]

Hi,

Can someone please tell me how to replace certain commas in a string with a semi-colon? I do not want to replace any of the other commas.

Example:
= MIN(MIN(R67595,R250000), R67574)+ SUM(R27215,R27218)
Should be:
= MIN(MIN(R67595,R250000), R67574)+ SUM(R27215:R27218)

The commas I want replaced will always be inside of parenthesis inside of the sum function.


A simple search and replace will not work.

Selection.Replace What:="5,r", Replacement:="0:r", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

 

[Joe4]

Assuming that you formula are always structured in that manner, here is one way. Select the range you want to apply it to and apply this VBA code:

Sub MyReplace()

    Dim cell As Range
    Dim f As String
    Dim newf As String
    Dim n As Long

    Application.ScreenUpdating = False
    
    For Each cell In Selection
        f = cell.Formula
        n = InStrRev(f, ",")
        If n > 1 Then
            newf = Left(f, n - 1) & ":" & Mid(f, n + 1)
            cell.Formula = newf
        End If
    Next cell

    Application.ScreenUpdating = True
    
End Sub

 

[Jonmo1]

Was that formula created by a previous piece of code?
Perhaps we can correct it there, before it's entered in the first place ?

 

[hgufrin]

Thanks for your help Joe 4.

I found an issue. There are a couple of cells that contained formulas that looked like this. The code chose to replace the last sum function of the string. The other commas remained.

= A56032+SUM(R56055,R56120)+SUM(R56170,R56190)+R56210+SUM(R56250:R56320)+R56370
= IF (SUM(R94884,R94935)+SUM(R95113,R95128)+SUM(R95230:R95242) NE 0) THEN 1 ELSE 0


Dim cell As Range
Dim f As String
Dim newf As String
Dim n As Long

Application.ScreenUpdating = False

For Each cell In Range("$c2:c" & Range("$a65536").End(xlUp).Row)
'For Each cell In Selection
f = cell.Formula
n = InStrRev(f, ",")
If n > 1 Then
newf = Left(f, n - 1) & ":" & Mid(f, n + 1)
cell.Formula = newf
End If
Next cell

Application.ScreenUpdating = True

 

[Joe4]

There are a couple of cells that contained formulas that looked like this. The code chose to replace the last sum function of the string. The other commas remained.

Yes, that is the danger of posting oversimplified questions or leaving out important details. My reply addresses the exact situations you listed in your original post.
You want to make sure that your questions are as detailed as possible, to address ALL the scenarios you need to address.

You need to define for us:
- all the possible different structure combinations you may encounter
- exactly what you want to happen in each of those different structures

 

[hgufrin]

Thanks Jonmo1,

No, the formula was not created by a previous piece of code... I tried this formula below. It did not work. This formula would ONLY replace the first sum function of the string. The other commas remained.

=IF(ISERROR(REPLACE(C3,SEARCH("sum(R",C3)+10,SEARCH("R",C3)-SEARCH("R",C3)+3,":")),C3,REPLACE(C3,SEARCH("sum(R",C3)+10,SEARCH("R",C3)-SEARCH("R",C3)+3,":"))

= R56032+SUM(R56055:6120)+SUM(R56170,R56190)+R56210+SUM(R56250,R56320)+R56370
= IF (SUM(R94884:4935)+SUM(R95113,R95128)+SUM(R95230,R95242) NE 0) THEN 1 ELSE 0

 

[hgufrin]

I understand Joe4.

Because there are thousands of lines of data...

All I know is that for every instance that a Comma "," is inside of a formula that contains Sum(R*,R*)... I would like that comma to be replaced with a colon. The asterisk is a wildcard. The row can be R1 thru R1048576.

If you can please make it work for the following formula... I think I will be good.
= R56032+SUM(R56055,6120)+SUM(R56170,R56190)+R56210+SUM(R56250,R56320)+R56370

 

[Joe4]

I was hoping to figure out a way to do it using FIND, REPLACE, or SUBSTITUTE with wildcards, but have not been able to get that to work.
This way might not be the most efficient, but will change any commas found within a SUM function to colons:

Sub MyReplace()

    Dim cell As Range
    Dim f As String
    Dim myLen As Long
    Dim i As Long
    Dim convert As Boolean

    Application.ScreenUpdating = False
    
    For Each cell In Selection
        convert = False
        f = cell.Formula
        myLen = Len(f)
        If myLen > 3 Then
            For i = 3 To myLen
'               Check to see if in middle of SUM formula
                If UCase(Mid(f, i - 2, 3)) = "SUM" Then
                    convert = True
                End If
'               If so and you ecounter a comma, change to colon
                If convert = True And Mid(f, i, 1) = "," Then
                    f = Left(f, i - 1) & ":" & Mid(f, i + 1)
                End If
'               If at end of function, change convert back to false
                If Mid(f, i, 1) = ")" Then convert = False
            Next i
            cell.Formula = f
        End If
    Next cell
        
    Application.ScreenUpdating = True
    
    MsgBox "Conversion complete!"
    
End Sub

 

[hgufrin]

Joe4,

This is PERFECT. It is a pity I have to abide by Forum Rules #5 . You people provide a valuable service to the industry.

I couldn't help but laugh when I saw the message box you incorporated.

Take care.

 

[Joe4]

This is PERFECT. It is a pity I have to abide by Forum Rules #5 . You people provide a valuable service to the industry.

You are welcome! Just pay it forward, we say!

I couldn't help but laugh when I saw the message box you incorporated.

Yeah, I often like to do that when loops are involved, as people often wonder, "Is it doing anything?", "Is it done?"