PDA

View Full Version : [SOLVED] Split Function Help



john3j
05-12-2015, 09:22 AM
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
05-12-2015, 09:31 AM
In B2, enter =TRIM(RIGHT(A2,LEN(A2)-FIND(":",A2))) then fill the formula.

john3j
05-12-2015, 11:52 AM
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
05-13-2015, 01:19 AM
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
05-13-2015, 04:25 AM
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 SubCharlize

john3j
05-14-2015, 11:00 AM
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
05-14-2015, 11:18 AM
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
05-14-2015, 11:48 AM
Hi john3j
Please ignore previous request - I have made progress. Will update you shortly
thanks

john3j
05-14-2015, 12:12 PM
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
05-14-2015, 02:08 PM
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
05-14-2015, 02:33 PM
@john3j
Charlize seems to have made good progess. I will now stop my work until you have tested Charlize's code

Yon

john3j
05-15-2015, 05:15 AM
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
05-15-2015, 10:11 AM
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
05-15-2015, 11:45 AM
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
05-15-2015, 12:28 PM
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
05-15-2015, 12:46 PM
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
05-19-2015, 05:44 AM
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
05-19-2015, 10:39 AM
Manufacturer = Trim(Split(Split(mynode.Text, "Computer Manufacturer : ")(1), "Computer Model :")(0))

Trim removes leading and ending spaces I believe.

Charlize