Split Function Help

[john3j]

I have a variable that stores multiline text, but I am trying to figure out how display a portion of the multiline text in a cell and didn't know if the split function would work? Here is a sample of the output of the variable:

Computer Manufacturer : Dell Inc.
Computer Model : OptiPlex 3010
Computer SerialNumber : AB45GH
Computer Type : Desktop

Here is what I am trying to achieve:

A2 = "Dell Inc."
B2 = "OptiPlex 3010"
B3 = "AB45GH"

Any Suggestions? I thought I could use the split function, but I don't know how to tell it to look in between, only after a delimeter. Any help would be greatly appreciated!

[JieJenn]

In B2, enter =TRIM(RIGHT(A2,LEN(A2)-FIND(":",A2))) then fill the formula.

[john3j]

That just puts all of the output onto one line. Do you know how to do it with VBA? I am trying to automate populating a workbook.

[Yongle]

Hi john3j

This should steer you in the right direction. Suggest you try running the macro in the attached workbook first to see if this is what you are what you are looking for, and then you can adapt it to suit your data.

A few message boxes included so that you can see what is happening to the variable as it passes thru the various stages in the code

1 Test data in column F is used to create string variable TextToSplit (line breaks added) - I have had to guess a little to create a variable that matches what you are trying to split, hopefully I am pretty close (you will not need this bit of the code when running the rest of the macro against your string variable later)
2 Field names and line breaks are stripped out of TextToSplit
3 Colons are used to split TextToSplitand the split values are placed in array WrdArray
4 Values from WrdArray are then placed in columns A to D starting at row 2, with a new row starting after every 4th value

Sub TextToSplit()

With ActiveSheet


'this bit creates variable similar to what I think you are trying to split
'based on values in column F of my worksheet
    Dim i As Integer
    Dim TextToSplit As String
        For i = 1 To 16
            TextToSplit = TextToSplit & .Cells(i, 6).Value & Chr(10)
        Next i
    MsgBox TextToSplit


'these lines remove the unecessary words and line breaks
    TextToSplit = Replace(TextToSplit, "Computer Manufacturer", "")
    TextToSplit = Replace(TextToSplit, "Computer Model", "")
    TextToSplit = Replace(TextToSplit, "Computer SerialNumber", "")
    TextToSplit = Replace(TextToSplit, "Computer Type", "")
    TextToSplit = Replace(TextToSplit, Chr(10), "")
    
    MsgBox TextToSplit


'this then splits the string into an array using the colon and space
    Dim WrdArray() As String
    WrdArray() = Split(TextToSplit, ": ")


'now transfer the values to the worksheet
    Dim r As Integer, a As Integer, k As Integer
'r = row number, k = column no, a = array element


    r = 2   'values start at row 2


    For a = 1 To UBound(WrdArray)
        k = k + 1
        .Cells(r, k) = WrdArray(a)
            If k = 4 Then  ' there are 4 items of data each time
                k = 0
                r = r + 1
            End If
    Next a


End With
End Sub

[Charlize]

paste this in a module, put a sample of info in a1 and run the code. It should display 4 messageboxes with the info you want.

Sub display_parts()
'rawdump is the dump of information, I've used cell A1
'myloop is a loop to cycle through the different parts of
'the multiline (i used a soft return as delimiter for the lines, a :
'for the info that you want to extract
Dim rawdump As String, myloop As Long
'fill rawdump with information
rawdump = ActiveSheet.Range("A1").Value
'display it in a msgbox
MsgBox rawdump
'loop through all the lines of the information, in this case 4
'lowest one is 0, highest linenumber = 3
'if you want to start with one, use option base 1 at top of coding


'split first time for lines
'inside loop, we split with : for the info we need
'there's a part 0 and 1 and we want part 1
For myloop = LBound(Split(rawdump, vbLf)) To UBound(Split(rawdump, vbLf))
    MsgBox Split(Split(rawdump, vbLf)(myloop), ":")(1)
Next myloop
End Sub

Charlize

[john3j]

I appreciate your feedback, but I am having trouble understanding how to apply what you are suggesting, so I thought it would be best if I gave you an example of my current code and sample output. So if I use the following code, I get "Dell Inc." followed by the computer Model, Serialnumber, Computer type, etc all in the same cell.
[CODE][Manufacturer = Split(myNode.Text, "Computer Manufacturer : ")(1)/CODE]

I am trying to lose the text after the output I need. Here is the output I am trying to get to:

B2 = "Dell Inc."
C2 = "OptiPlex 320"
D2 = "1.1.5"
H2 = "ABC12FG"
J2 = "Microsoft Windows XP Professional Service Pack 3 (English)"

I cannot provide you a source file, only the output. Please let me know if you can help?

[Yongle]

Hi john3j

It would be easier I think if I started at the beginning of the process, rather than part the way through.
How is the data getting into excel? Is there a source text file? Or another type of file? If there is, please attach it to your reply

thanks
Yon

[Yongle]

Hi john3j
Please ignore previous request - I have made progress. Will update you shortly
thanks

[john3j]

Hi john3j
Please ignore previous request - I have made progress. Will update you shortly
thanks

Please see the attached file. I appreciate your help!

[Charlize]

A partial rewritten part of your coding to extract the computer firm, the model and serial number. Use with caution, I couldn't test it because I don't have the original import files. I rewrote the part of your manufacturer information code.

'Get manufacturer informationSet mynode = oXMLFile.SelectSingleNode("/NessusClientData_v2/Report/ReportHost/ReportItem[@pluginID='24270']/plugin_output")
If Not mynode Is Nothing Then
    '*** changed stuff, loop through the parts of the initial mynode.text --- parsed from xml
    Dim mylines As Long
    For mylines = LBound(Split(mynode.Text, vbLf)) To UBound(Split(mynode.Text, vbLf))
        'the idea is to look for the word Manufacturer in the splitted lines of your information
        'if found, the info is stored in your variable Manufacturer
        If Trim(Split(Split(mynode.Text, vbLf)(mylines), ":")(0)) = "Manufacturer" Then
            Manufacturer = Split(Split(mynode.Text, vbLf)(mylines), ":")(1)
        End If
    'Manufacturer = Split(mynode.Text, "Computer Manufacturer : ")(1)
    'Manufacturer = myNode.Text
    'ModelNum = Split(mynode.Text, "Computer Model : ")(1)
        'here we search for the word Computer Model in the left part of splitted line of the information
        If Trim(Split(Split(mynode.Text, vbLf)(mylines), ":")(0)) = "Computer Model" Then
            ModelNum = Split(Split(mynode.Text, vbLf)(mylines), ":")(1)
        End If
    'SerialNum = Split(mynode.Text, "Computer SerialNumber : ")(1)
        'here we look for the word Computer SerialNumber
        If Trim(Split(Split(mynode.Text, vbLf)(mylines), ":")(0)) = "Computer SerialNumber" Then
            SerialNum = Split(Split(mynode.Text, vbLf)(mylines), ":")(1)
        End If
    Next mylines
    'MsgBox Manufacturer
    'MsgBox ModelNum
    'MsgBox SerialNum
End If

Charlize

[Yongle]

@john3j
Charlize seems to have made good progess. I will now stop my work until you have tested Charlize's code

Yon

[john3j]

Charlize,

Thank you so much for your help. When I execute your code, I get the following:

Run-time error '9': Subscript out of range. It highlights the following line:

If Trim(Split(Split(mynode.Text, vbLf)(mylines), ":")(0)) = "Manufacturer" Then

I have attached the workbook I have been working with, along with a zip file that contains .nessus file to test with.

Also, if you could tell me what the (0) and (1) after the split function means, that would be appreciated. I cant seem to find it documented anywhere.

Thanks,
John

[Charlize]

Think the problem has to do with the carriage returns (if there even are line breaks). I thought the lines were divided by a soft return (vblf) instead of a hard line return (vbcrlf). So each line was splitted using vblf (was my idea). Doesn't work obviously (or error in my coding).

To address the 0 or 1 after using split :

- if you got a line with one : in it you could use split to create an array (text only is the resulting array)
- there is a 0 (first element of array) and 1 (second part of array)
- if you don't know how many items you would have , you loop through all the items with lbound (lowest value) to ubound (highest value)
- during this time you can compare each item

- in this case i thought splitting with soft return would divide all the lines in an array
- so mylines would be the loop to go through the array
- then each line is divided by : in a 0 and 1 part
- if 0 part contains manufacturer or computer model or … what you look for, then 1 is the part you want to have.

can you find the values inside that .nessus file ? And maybe strip the info you want from that file.

Charlize

[Charlize]

Ok, different approach . Let's see if this works … does work with stored data in b2 so I hope it will work with mynode.txt too .

Sub testing_stuff() 'Get manufacturer informationSet mynode = oXMLFile.SelectSingleNode("/NessusClientData_v2/Report/ReportHost/ReportItem[@pluginID='24270']/plugin_output")
'***
'for testing purposes I stored B2 in a variable
'I think you need to alter every mynode into mynode.txt if you want to use this in your coding
'and delete next line and messageboxes or put ' in front of the line
mynode = ActiveSheet.Range("B2").Text
'If Not mynode Is Nothing Then
     '*** changed stuff, now I'm looking for keywords inside mynode.text --- parsed from xml
    'this one is is the variable with all the stuff extracted from that xml file
    MsgBox Split(mynode, ":")(0)
    'just testing
    MsgBox Split(mynode, "Manufacturer :")(1)
    'Here I look for the keyword Manufacturer : --- since this keyword is 14 letters long I add 14 positions to the start
    'of the info we want
    'Than I look for the second keyword in your file and subtract the beginning - 14
    'so if 2nd keyword starts at 53 and first is 30 than we have : start 30 + 14 = 44 --- end = 53 - 44 = length of data we search
    MsgBox Mid(mynode, InStr(1, mynode, "Manufacturer :") + 14, _
                        InStr(1, mynode, "Computer Model :") - InStr(1, mynode, "Manufacturer :") - 14)
    Manufacturer = Mid(mynode, InStr(1, mynode, "Manufacturer :") + 14, _
                                    InStr(1, mynode, "Computer Model :") - InStr(1, mynode, "Manufacturer :") - 14)
    MsgBox Manufacturer
    MsgBox Mid(mynode, InStr(1, mynode, "Computer Model :") + 16, _
                        InStr(1, mynode, "Computer SerialNumber :") - InStr(1, mynode, "Computer Model :") - 16)
    MsgBox Mid(mynode, InStr(1, mynode, "Computer SerialNumber :") + 23, _
                        InStr(1, mynode, "  ") - InStr(1, mynode, "Computer Serialnumber :") - 23)
     'MsgBox Manufacturer
     'MsgBox ModelNum
     'MsgBox SerialNum
'End If
End Sub

Charlize

[john3j]

Charlize,

I will definitely try that and see what happens. I did have a thought though. I am splitting the string initially before it is dumped into the workbook. For instance, I have

Manufacturer = Split(myNode.Text, "Computer Manufacturer : ")(1)

which gives me the following output in my worksheet:

Dell Inc.
Computer Model : OptiPlex 755
Computer SerialNumber : ABCXDH1
Computer Type : Mini Tower
Computer Physical CPU's : 1
Computer Logical CPU's : 2
CPU0
Architecture : x86
Physical Cores: 2
Logical Cores : 2
Computer Memory : 3325 MB

Form Factor: DIMM
Type : Unknown
Capacity : 2048 MB

Form Factor: DIMM
Type : Unknown
Capacity : 2048 MB

Why couldn't we use a sub to split it again to isolate the text I am needing. So why not do something like

Split(tempVar.Text, "Computer Model : ")(0)

to get the output "Dell Inc." and then paste it back in the cell? I started to write a sub that does this, but I am struggling with and error (Run-time error '424': Object required). Here is the code I wrote that I wanted to run after my workbook was already populated, which in my mind would go through each row and split the text within the cells to give me the output I am looking for.

[CODE][Sub splitText()
Dim tempVar, compMan, compMod, compFW, compSN, compOS As String
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To FinalRow
tempVar = Cells(i, "B").Value
If Not tempVar Is Nothing Then
compMan = Split(tempVar.Text, "Computer Model : ")(0)
Cells(i, "B").Value = compMan
End If
tempVar = Cells(i, "C").Value
If Not tempVar Is Nothing Then
compMod = Split(tempVar.Text, "Computer SerialNumber : ")(0)
Cells(i, "C").Value = compMod
End If
tempVar = Cells(i, "D").Value
If Not tempVar Is Nothing Then
compFW = Split(tempVar.Text, "Release date : ")(0)
Cells(i, "D").Value = compFW
End If
tempVar = Cells(i, "H").Value
If Not tempVar Is Nothing Then
compSN = Split(tempVar.Text, "Computer Type : ")(0)
Cells(i, "H").Value = compSN
End If
tempVar = Cells(i, "J").Value
If Not tempVar Is Nothing Then
compOS = Split(tempVar.Text, "Confidence Level : ")(0)
Cells(i, "J").Value = compOS
End If
Next i
End Sub
/CODE]

I will try what you have and let you know where I get. For the purposes of learning, do you think you could help me figure out my error with the sub I was trying to write?

[Charlize]

Manufacturer = Split(Split(mynode.Text, "Computer Manufacturer : ")(1), "Computer Model :")(0)

will give Dell Inc. I think ...

ComputerModel = Split(Split(mynode.Text, "Computer Model :")(1), "Computer SerialNumber :")(0)

will give Optiplex

ComputerSerial = Split(Split(mynode.Text, "Computer SerialNumber :")(1), "Computer Type :")(0)

will give the serialnumber

Charlize

[john3j]

Charlize, that worked perfectly. I used the same idea for the firmware and the operating system. I just have to figure out how to get rid of the three spaces that are left after the output in each of the cells where split text is. Thank you again!

[Charlize]

Manufacturer = Trim(Split(Split(mynode.Text, "Computer Manufacturer : ")(1), "Computer Model :")(0))

Trim removes leading and ending spaces I believe.

Charlize