r/vba • 1 • Nov 05 '17

VBA- extracting part of cells (CHALLENGING)

NOT SOLVED YET, So I'm trying to extract the Site ID #'s from a table similar to below. Sometimes the table is 10 rows, other times its 50-100. And then there's usually around 10-20 Site IDs in each of the cells I'm looking at which is why this process needs to be automated.

I have some code written (see below), however, it's not quite doing what I need. It seems to be skipping over some of the Site IDs and is also not separating out the Site ID from the Equipment ID. The Site ID should be the first 8 characters (although sometimes I've seen it inputted incorrectly with 9 characters) so either an error handler or a way to account for the strings to be a variant of 8 characters built in would be ideal.

Some other points to keep in mind is not only do I want to export/print out all of the Site IDs I also want to get the Start date, End date, Change ID, etc (which would remain constant per row) as well. I imagine this would work using an offset function. I didn't include this but these Values would be stored in other columns. The Site Ids are found in column K...Any help?

[K1] Description*

[K2] Answer the 3 required items below (please provide answers at end of each item): 1. Brief Description of Change (WHAT is being done): Note time is entered in Central Time Zone for a Eastern Time Zone Location The local market has approved day time work due to Safety, Access and Escort Issues Cutover the fALU BBU to the Nokia FSM4 BBU at the following sites: Site ID # Equipment ID # PAL00676 346L00676 PAL00649 346L0649 PAL00653 346L0653 PAL01942 346L1942 DEL05019 346L5019 DEL05023 346L5023 PAL01033 346L1033 PAL01090 346L1090 PAL01099 346L1099 PAL01022 346L1022 PAL04022 181L4022 2. Business Reason (WHY is this change being performed): Nokia FSM4 trial. Validate new Nokia FSM4 BBU for upcoming 2017 deployment nationwide. Note: CR Request exceeds 7 days Per the NPMO Manager

[K3] Answer the 3 required items below (please provide answers at end of each item): 1. Brief Description of Change (WHAT is being done): Note time is entered in Central Time Zone for a Eastern Time Zone Location The local market has approved day time work due to Safety, Access and Escort Issues Cutover the fALU BBU to the Nokia FSM4 BBU at the following sites: Site ID # Equipment ID # PAL06115 483L6115 PAL04102 181L4102 PAL04048 181L4048 PAL04124 181L4124 PAL04108 181L4108 PAL04026 181L4026 PAL04502 181L4502 PAL04035 181L4035 PAL04011 181L4011 PAL04027 181L4027 PAL04116 181L4116 PAL04127 181L4127 PAL00514 346L0514 PAL00661 346L0661 PAL00537 346L0537 DEL05009 346L5009 DEL05096 346L5096 DEL05021 346L5021 DEL05078 346L5078 DEL05022 346L5022 PAL01030 346L1030 PAL01092 346L1092 PAL01029 346L1029 PAL01031 346L1031 PAL01108 346L1108 PAL04021 181L4021 PAL04017 181L4017 PAL04004 181L4004 PAL04016 181L4016 PAL04114 181L4114 PAL04104 181L4104 PAL04147 181L4147 PAL04112 181L4112 PAL04030 181L4030 PAL04001 181L4001 PAL04043 181L4043 PAL04331 181L4331 2. Business Reason (WHY is this change being performed): Nokia FSM4 trial. Validate new Nokia FSM4 BBU for upcoming 2017 deployment nationwide. Note: CR Request exceeds 7 days Per the NPMO Manager

[K4] Answer the 3 required items below (please provide answers at end of each item): 1. Brief Description of Change (WHAT is being done): Note time is entered in Central Time Zone for a Eastern Time Zone Location The local market has approved day time work due to Safety, Access and Escort Issues Cutover the fALU BBU to the Nokia FSM4 BBU at the following sites: Site ID # Equipment ID # PAL06078 483L6078 PAL00650 346L0650 2. Business Reason (WHY is this change being performed): Nokia FSM4 trial. Validate new Nokia FSM4 BBU for upcoming 2017 deployment nationwide. Note: CR Request exceeds 7 days Per the NPMO Manager

and many more of the same below... At least 30 rows of similar data above.

my VBA Code:

Sub exampleUsage2()

Dim idList As Variant
Dim lastRow As Long
idList = extractIDs(ActiveWorkbook.Sheets(1).Range("k2:k3"))

ThisWorkbook.Activate

With ThisWorkbook.Sheets("Destination sheet")
.Range("A1").Value = "Site ID"
.Range("B1").Value = "Original row"
.Range(.Cells(2, 1), .Cells(UBound(idList, 1) + 1, 2)).Value = idList
End With
End Sub

Function extractIDs(rng As Range)

Dim columnArr As Variant
columnArr = rng.Value

Dim startID As String
Dim endID As String
startID = "Equipment ID #" '"Equipment ID #" 'Equipment ID
endID = "Business Reason"

Dim arrSize As Long
Dim i As Long
For i = LBound(columnArr, 1) To UBound(columnArr, 1)
Dim startPos As Long
Dim endPos As Long
startPos = InStr(columnArr(i, 1), startID) + Len(startID) + 1
endPos = InStr(columnArr(i, 1), endID) - 2
columnArr(i, 1) = Mid(columnArr(i, 1), startPos, endPos - startPos + 1)
arrSize = arrSize + ((Len(columnArr(i, 1)) - Len(Replace(columnArr(i, 1), " ", ""))) + 1) /2
Next


ReDim resultsArr(1 To arrSize, 1 To 2) As String
Dim j As Long
j = 1
For i = LBound(columnArr, 1) To UBound(columnArr, 1)
Dim goAhead As Boolean
goAhead = False
Do Until goAhead = True
Dim firstSpace As Long
Dim nextSpace As Long
firstSpace = InStr(columnArr(i, 1), " ")
If firstSpace = 0 Then
goAhead = True
Else
'Stores ID
resultsArr(j, 1) = Left(columnArr(i, 1), firstSpace - 1)
'Stores original row
resultsArr(j, 2) = i + (rng.Row - 1)
j = j + 1
nextSpace = InStr(firstSpace + 1, columnArr(i, 1), " ")
If nextSpace = 0 Then
goAhead = True
Else
columnArr(i, 1) = Right(columnArr(i, 1), Len(columnArr(i, 1)) - nextSpace)
End If
End If
Loop
Next

extractIDs = resultsArr

End Function
0 Upvotes

12 comments sorted by

2

u/nolotusnotes Nov 05 '17

If you're determined to do this in VBA, the Split function is going to be your best friend.

The reason I question using VBA is because Power Query would make this much easier to automate.

1

u/mrbigbluff21 1 Nov 05 '17

Tell me more, what's a power query and how will it be used?

1

u/nolotusnotes Nov 05 '17

Power Query is a powerful add-in for Excel made by Microsoft. You can download it from Microsoft here:

https://www.microsoft.com/en-us/download/details.aspx?id=39379

Sample of some trickery:

https://www.youtube.com/watch?v=dTaex7IFb70

1

u/mrbigbluff21 1 Nov 05 '17

I'll have to check it out.. But what you're saying is that you would do it that way over vba?

1

u/nolotusnotes Nov 05 '17

I would do it in Power Query for efficiency. It can do in a few clicks what takes hundreds of lines of VBA to accomplish.

1

u/mrbigbluff21 1 Nov 05 '17

A couple reasons I dont think this will work. 1) the data within the cells of the column I am looking doesnt always contain the same amount of characters/Site IDS. Also the other reason is that this needs to be replicated many times by an end user (not myself). So having them build out a power query every time is not sustainable. I think I would still prefer VBA on this one.

1

u/nolotusnotes Nov 05 '17

I'm having a really hard time understanding the exact layout/structure of your file. Regular Reddit text formatting murdered your sample.

Power Query is built directly into Excel 2016, so it's worth learning.

1

u/mrbigbluff21 1 Nov 05 '17

Yea i get why it might be hard to understand. 1. I'm terrible at explaining and 2. the cells have thousands of text characters found within. Which is why I want to automate this process.

Basically Its a bunch of text that we need to ignore. The only useful piece within the text of the cells is the words, "Site ID # Equipment ID # " because this is where the VBA script will know to "start". After that point, there will be an unknown amount of Site IDs that I am trying to extract out of the cell. The important piece of information to gather from this is that the Site IDs should always be the first 8 characters while the equipment ID is the next 8. I only need the Site IDs. Perhaps the last piece of information to know after this is that the VBA code will know to stop at the words, "2. Business". Do we need to even have a stopping point??? Lastly I do want to point out that not only do I need each Site ID extracted, I'd like to also gather some other information out of the other Columns which would remain constant within all of the Site IDs found in that row. However, there could be 20-100 rows within the file. Hope that all helps.

1

u/mrbigbluff21 1 Nov 05 '17

Also it might appear like reddit is throwing a bunch of values together and its hard to read. In reality what I pasted above would be a 1x3 table. 1 column and 3 rows. Each cell has a large amount of text contained inside and I just want to extract the "Site IDs" out of the cells.

2

u/nolotusnotes Nov 06 '17

Ok, I'm going to throw some high-end shit at you. Because that's what you need to do this project correctly.

To do this properly, you're going to want to use Regular Expressions. Not really a part of VBA, but it's usable on any Windows machine via VBA.

I pasted a single cell of your data into an on-line parser and fiddled with the Regular Expression until I got the right results:

https://regex101.com/r/eZ1gT7/1158

This gets you all of the text between the starting and ending text in each cell.

Next, I used that formula in VBA code. Only on one cell, mind you. You'd have to iterate by line in your Excel Sheet on that column. Yeah, you have some work to do yet.

So, let's parse one cell of code...

Sub testit()
    Dim celltext As String

    celltext = Sheets("Sheet1").Cells(3, 11).Value


    x = Join(GetListOfValues(celltext), ",")

   MsgBox x

End Sub
    
Function GetListOfValues(strInput)
    Dim objRegEx, oRegResults
    Set objRegEx = CreateObject("VBScript.RegExp")
    objRegEx.IgnoreCase = True
    objRegEx.MultiLine = True
    objRegEx.Global = True
    objRegEx.Pattern = "Equipment ID # (.*) 2. Business"
    Set oRegResults = objRegEx.Execute(strInput)
    If oRegResults.Count = 0 Then Exit Function
    If oRegResults(0).SubMatches.Count = 0 Then Exit Function
    GetListOfValues = Split(oRegResults(0).SubMatches(0), vbCrLf)
End Function

This should get you well on your way.

0

u/[deleted] Nov 07 '17

[deleted]

1

u/marcelowit Nov 11 '17

formatting may be way off as the editor is dreadful.

Dude I feel your pain