How to use a Macro to run through sheet in excel and put double quotes around values

How to use a macro to run through sheet in excel and put double quotes around values

Requirement:

My excel has been culled down from 140000 lines to a few thousand through filters. I cut out the few thousand and pasted to new sheet. I have values such as the below ... I want to go line by line through the sheet and add quotations around any value after an equal sign. Many of the values (especially the more difficult values that have multiple words) already have quotes so that headache is out. I do have concrete variables that equals those values, such as macharea as a variable =71477.9572, etc. If it could loop through and just find an equal sign and quote around the value following up to the first space, that would be ideal. I believe I could also name the variable and code in to quote it's value possibly. I would appreciate any tips on how I can accomplish the final goal of quotes around all values. The reason for this objective is to then convert this into legitimate xml. xml requires quotes around any value.

Sample data:

<assycount=3778 drillcount=24 macharea=22623086.57295438 machcount=4907 name="21-00325.003" taskcount=351 taskhours=1571.45481423 usetasksforassembly=false>

StephenGunn, the user requires the following: "I don't know how to go about this and am not sure it can be done with excel. I've done quite a lot with functions and macros in excel in the past. To me this one is a challenge and I've found not solid examples of anything at all similar out on the net.

Process is this. Export xml from a Die Build Process Planner COTS application called SMIRT. The xml is not true xml and cannot be read in an xml reader. I could not import it as xml into excel. In my case I spent about 30 hours writing regular expressions in notepad ++ and doing replace and finds. I did write this regular expression \b(\w+)=((?:\s*[^=>]+\b(?!=))+)?(\s+|\/?>) $1="$2"$3 which got me mostly there but was double writing quotes on some of the values (not all) that already had quotes. I rewrote that regular expression a few times. In mean time a coworker showed me he could past into excel the structure and excel logically indented (followed the indenting) of the structured xml … we have 10 columns and created filters on each and were able to filter out unncessary content. I had ten macros in regex that accomplished this in notepad++ but this was a much quicker process. I wanted it in excel because I've been able to write macros in the past for many needs. I felt confident I’d be able to write something for this but nothing I found fit the bill to do what I needed here.

Very specifically, I was looking a way, and I'm not at sure if even possible which is the challenge, to go through the cells in the excel and find every location of an equal sign. If the equal value to the equal sign already has quotes to ignore that value. If the value of the equal sign does not have quotes to put quotes around that value. The value end would be identified by the first space found. Thus myvariable=myvalue mysecondvariable=myvalue2, etc. Many of these variables and values would be found in the same cell."

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/1212785-macro-to-run-through-sheet-in-excel-and-put-double-quotes-around-values

Solution:

Code:

Sub AddQuotes()
    Dim x, y, i As Long, ii As Long, iii As Integer, s As String
    
    x = Sheet1.Cells(1).CurrentRegion.Offset(1, 1)
    For i = 1 To UBound(x, 1)
        For ii = 1 To UBound(x, 2)
            If x(i, ii) <> vbNullString Then
                y = Split(x(i, ii))
                x(i, ii) = vbNullString
                If Not IsArray(y) Then
                    x(i, ii) = y
                Else
                    For iii = LBound(y) To UBound(y)
                        If InStr(1, y(iii), Chr(61)) Then
                            s = Split(y(iii), Chr(61))(1)
                            If Left(s, 1) <> Chr(34) Then
                                If Right(s, 1) = Chr(62) Then
                                    s = Chr(34) & Left(s, Len(s) - 1) & Chr(34) & Chr(62)
                                Else
                                    s = Chr(34) & s & Chr(34)
                                End If
                                x(i, ii) = x(i, ii) & " " & Split(y(iii), "=")(0) & Chr(61) & s
                            Else
                                x(i, ii) = x(i, ii) & " " & y(iii)
                            End If
                        Else
                            x(i, ii) = x(i, ii) & " " & y(iii)
                        End If
                    Next
                    x(i, ii) = Trim(x(i, ii))
                End If
            End If
        Next
    Next
    With Sheet2
        .Cells.Clear
        .[a1].Resize(UBound(x, 1), UBound(x, 2)) = x
        .Activate
    End With
    
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by KjBox.