VBA that can extract words that starts with # and ends with 2021.

[whyjaydee]

Hi Everyone,

I am straggling to in searching for a vba code that will extract a string that starts with # and ends with 2021. The string has no specific length.
After extracting, I need it to be in one column listed. There can be multiple words that starts with # and ends with 2021 in one cell, note that the 1 cell for example cell A1, contains a paragraph long texts and within that text the code will search for all the words that starts with # and ends with 2021 and put it in another column. If the code found found more than 1 word that starts with # and ends with 2021, then it has to be listed per cell in 1 column.

Example
Here is the paragraph or bunch of words and characters in cell A1:

{"106835224676040":{"id":"106835224676040","time":1613053377,"author":"Luisito Noel Magleo","text":"#RRT2106102112021","highlighted":true,"type":"user"},"106830671343162":{"id":"106830671343162","time":1613052698,"author":"Paul John Honrubia","text":"#RRT_PaulJohnHonrubia","highlighted":true,"type":"user"},"#RRT2106102112021":{"id":"105041188188777","time":1612709664,"author":"Arnel Ramo","text":"#RRT1570702072021","highlighted":true,"type":"user"},"103766651649564":{"id":"103766651649564","time":1612463717,"author":"Algernon Sionosa","text":"

The string in Red font are the words that I need to get in the bunch of characters and needs to be listed in another column like this
Cell B1: #RRT2106102112021
Cell B2: #RRT2106102112021
Cell B3: #RRT1570702072021

Note that the words I'm looking for can appear repeatedly in the same paragraph

I appreciated all kinds of help.

 

[Rick Rothstein]

I missed that you were going to have multiple cells in Column A. Give this a try then...

Sub SplitPound2021TextFromA1ToB1Down()
  Dim N As Long, X As Long, Cell As Range, Arr1 As Variant, Arr2 As Variant
  Columns("B").Clear
  For Each Cell In Range("A1", Cells(Rows.Count, "A"))
    Arr1 = Split(Cell, "#")
    For X = 1 To UBound(Arr1)
      If Arr1(X) Like "*2021*" Then
        Arr2 = Split(Arr1(X), 2021)
        N = N + 1
        Cells(N, "B") = "#" & Arr2(0) & 2021
      End If
    Next
  Next
End Sub

 

[whyjaydee]

I missed that you were going to have multiple cells in Column A. Give this a try then...

Sub SplitPound2021TextFromA1ToB1Down()
  Dim N As Long, X As Long, Cell As Range, Arr1 As Variant, Arr2 As Variant
  Columns("B").Clear
  For Each Cell In Range("A1", Cells(Rows.Count, "A"))
    Arr1 = Split(Cell, "#")
    For X = 1 To UBound(Arr1)
      If Arr1(X) Like "*2021*" Then
        Arr2 = Split(Arr1(X), 2021)
        N = N + 1
        Cells(N, "B") = "#" & Arr2(0) & 2021
      End If
    Next
  Next
End Sub

Thank you Rick,

I tried using the this new code, but the result seems to be skipping some cell:

It was able to extract 2 counts of # from cell A2, then it skips cell A3, A4, A5.
There are #s in those cells which was not extracted.

Thanks again Rick.
You've been very helpful.

 

[Rick Rothstein]

Can you post those first 10 cell values so I can copy/paste them into an Excel worksheet (I cannot work with a picture)?

 

[whyjaydee]

I fig

Thank you Rick,

I tried using the this new code, but the result seems to be skipping some cell:

View attachment 33262

It was able to extract 2 counts of # from cell A2, then it skips cell A3, A4, A5.
There are #s in those cells which was not extracted.

Thanks again Rick.
You've been very helpful.I

Can you post those first 10 cell values so I can copy/paste them into an Excel worksheet (I cannot work with a picture)?

Surely Rick,
Here you go!
Header is called Raw Data and this is up to 20 rows including the header.

Raw Data​

{"106835224676040":{"id":"106835224676040","time":1613053377,"author":"Luisito Noel Magleo","text":"#RRT2106102112021","highlighted":true,"type":"user"},"106830671343162":{"id":"106830671343162","time":1613052698,"author":"Paul John Honrubia","text":"#RRT_PaulJohnHonrubia","highlighted":true,"type":"user"},"105041188188777":{"id":"105041188188777","time":1612709664,"author":"Arnel Ramo","text":"#RRT1570702072021","highlighted":true,"type":"user"},"103766651649564":{"id":"103766651649564","time":1612463717,"author":"Algernon Sionosa","text":"Used Knowledge Base article [Ad Account Admin: Status: Policy Disable 2532703386955916]","highlighted":true,"type":"system"}}​

{"107843927893513":{"id":"107843927893513","time":1612782282,"author":"Edward Rodriguez","text":"#RRT_Edward","highlighted":true,"type":"user"}}​

{"110962290903389":{"id":"110962290903389","time":1613071012,"author":"Joey Naluz","text":"#RRT1971702112021","highlighted":true,"type":"user"},"110890910910527":{"id":"110890910910527","time":1613059489,"author":"Denise Relativo","text":"#RRT2467502112021\n","highlighted":true,"type":"user"},"108847007781584":{"id":"108847007781584","time":1612675704,"author":"Ronnel Abella Villa","text":"#RRT1052702072021 ","highlighted":true,"type":"user"},"107772727889012":{"id":"107772727889012","time":1612468420,"author":"Aaron Narido","text":"Used Knowledge Base article [Ads Creation and Editing: Publishing Error Messages: Generic Publishing Error 755908341626631]","highlighted":true,"type":"system"},"107771234555828":{"id":"107771234555828","time":1612468142,"author":"Aaron Narido","text":"Used Knowledge Base article [A\/B Tests 637615473669592]","highlighted":true,"type":"system"}}​

{"114438903893508":{"id":"114438903893508","time":1613643470,"author":"Lorrena Manzano","text":"#RRT2401602092021 ","highlighted":true,"type":"user"}}​

{"111828034154192":{"id":"111828034154192","time":1613206735,"author":"Diego Odchimar III","text":"#RRT2522302132021","highlighted":true,"type":"user"},"111211067549222":{"id":"111211067549222","time":1613088925,"author":"Ronnel Abella Villa","text":"#RRT1052702122021 ","highlighted":true,"type":"user"}}​

{"113442170650092":{"id":"113442170650092","time":1612863057,"author":"Nicole Anthony Cruz","text":"#RRT2308002092021\n","highlighted":true,"type":"user"},"111541210840188":{"id":"111541210840188","time":1612512857,"author":"Marie Joy Loy-a","text":"Used Knowledge Base article [Why is My Boost Unavailable? 296238434398225]","highlighted":true,"type":"system"}}​

{"117739536876014":{"id":"117739536876014","time":1613289372,"author":"Dona Lyn Anagaran","text":"#RRT1495302142021","highlighted":true,"type":"user"},"117326833583951":{"id":"117326833583951","time":1613214877,"author":"Ma Elisa Viceno","text":"#RRT1754602132021\n","highlighted":true,"type":"user"},"113689110614390":{"id":"113689110614390","time":1612531333,"author":"Angelique Garcia","text":"Used Knowledge Base article [Pages Operations: Page Policy: Feature Block\/Appeal Feature Block 375070306718862]","highlighted":true,"type":"system"}}​

{"118414720177748":{"id":"118414720177748","time":1612810782,"author":"Joey Naluz","text":"#RRT1971702082021","highlighted":true,"type":"user"},"118341543518399":{"id":"118341543518399","time":1612802542,"author":"Edward Rodriguez","text":"#RRT_Edward","highlighted":true,"type":"user"}}​

{"119465593413781":{"id":"119465593413781","time":1612823268,"author":"Antonette Gay Wao","text":"#RRT1918202082021\n#EmailSent\n","highlighted":true,"type":"user"},"118782443482096":{"id":"118782443482096","time":1612724892,"author":"Jerome Victor Lagdamen","text":"#RRT_JErome","highlighted":true,"type":"user"},"117441500282857":{"id":"117441500282857","time":1612528860,"author":"Marlon Dela Cruz","text":"Used Knowledge Base article [Business Manager Admin: Access: Admin Permission 1756768327766996]","highlighted":true,"type":"system"}}​

{"121036673241921":{"id":"121036673241921","time":1612780485,"author":"Ronnie Naranjo","text":"#RRT_Ronnie","highlighted":true,"type":"user"},"119070556771866":{"id":"119070556771866","time":1612476369,"author":"Rizaldee John Caburog","text":"#SME12535_02052021 \n#InternalSMEApproved\n","highlighted":true,"type":"user"},"119066963438892":{"id":"119066963438892","time":1612475662,"author":"Billie Jane Maglangit","text":"Ad Account Admin: Status: Banhammer","highlighted":true,"type":"user"},"119066756772246":{"id":"119066756772246","time":1612475618,"author":"Billie Jane Maglangit","text":"#Refund Request","highlighted":true,"type":"user"},"119066606772261":{"id":"119066606772261","time":1612475593,"author":"Billie Jane Maglangit","text":"Working Refund Escalations","highlighted":true,"type":"user"}}​

{"121375886540392":{"id":"121375886540392","time":1612786582,"author":"Mark Anthony Doroin","text":"#RRT_MarkAnthonyDoroin","highlighted":true,"type":"user"},"119364293408218":{"id":"119364293408218","time":1612474060,"author":"Kenneth Fernandez","text":"#EMAILER1240102042021","highlighted":true,"type":"user"},"119364203408227":{"id":"119364203408227","time":1612474040,"author":"Kenneth Fernandez","text":"Used Knowledge Base article [Ad Policy: Appeal: Ad Disapproval 378262183118562]","highlighted":true,"type":"system"}}​

{"124424989567954":{"id":"124424989567954","time":1613147478,"author":"Michael Kaven Tubera","text":"#RRT2117102132021\n","highlighted":true,"type":"user"},"124424952901291":{"id":"124424952901291","time":1613147473,"author":"Michael Kaven Tubera","text":"No action taken. Owner responded. #RRT2117102132021\n","highlighted":true,"type":"user"},"119507970059656":{"id":"119507970059656","time":1612461342,"author":"Maria Jessica Martinez","text":"Used Knowledge Base article [Facebook Shops\/Instagram Shopping: Onboarding Process 1425113921029729]","highlighted":true,"type":"system"},"119507890059664":{"id":"119507890059664","time":1612461329,"author":"Maria Jessica Martinez","text":"Used Knowledge Base article [Facebook Shops\/Instagram Shopping: Shop Builder: Publish shop 338705273775209]","highlighted":true,"type":"system"},"119507873392999":{"id":"119507873392999","time":1612461325,"author":"Maria Jessica Martinez","text":"Used Knowledge Base article [Facebook Shops\/Instagram Shopping: Onboarding Process: Cannot connect to Facebook Page or catalog 819867325170970]","highlighted":true,"type":"system"}}​

{"130719312269714":{"id":"130719312269714","time":1614152900,"author":"Jennifer Sanchez","text":"#RRT871702242021","highlighted":true,"type":"user"},"119835473358098":{"id":"119835473358098","time":1612527984,"author":"Marestil Loayon","text":"Used Knowledge Base article [Ad Account Admin: Status: Policy Disable 2532703386955916]","highlighted":true,"type":"system"}}​

{"122564879749010":{"id":"122564879749010","time":1612728133,"author":"Joelle Enya Bacolod","text":"#RRT_Joelle\n#EmailSent\n","highlighted":true,"type":"user"},"120805663258265":{"id":"120805663258265","time":1612465251,"author":"Mary Anne Dela Cruz","text":"Used Knowledge Base article [Ad Account Admin: Status: Risk Disable 434109300676044]","highlighted":true,"type":"system"}}​

{"132367778766177":{"id":"132367778766177","time":1614207856,"author":"Joey Naluz","text":"#RRT1971702242021","highlighted":true,"type":"user"},"127290492607239":{"id":"127290492607239","time":1613489310,"author":"Allysa Borden","text":"#RRT1739902162021\n","highlighted":true,"type":"user"},"126794965990125":{"id":"126794965990125","time":1613412659,"author":"Edward Rodriguez","text":"#RRT1928202162021","highlighted":true,"type":"user"},"122768556392766":{"id":"122768556392766","time":1612803794,"author":"Shin Joon Franck Hermedia","text":"#RRT1498602082021\n","highlighted":true,"type":"user"},"121135696556052":{"id":"121135696556052","time":1612547138,"author":"David Cudilla","text":"Used Knowledge Base article [Instagram Promote: Promote Flow: Promote Unavailable 2486152838141504]","highlighted":true,"type":"system"}}​

{"124550626220646":{"id":"124550626220646","time":1612977259,"author":"Jerome Victor Lagdamen","text":"#RRT_20689021021","highlighted":true,"type":"user"},"123073096368399":{"id":"123073096368399","time":1612779602,"author":"Princess Anne Beth Banton","text":"#RRT_Princess","highlighted":true,"type":"user"},"121455523196823":{"id":"121455523196823","time":1612540535,"author":"Karlo Kristofer Cruz","text":"Used Knowledge Base article [Partner Monetization Policies 272106463588858]","highlighted":true,"type":"system"}}​

{"123739199564108":{"id":"123739199564108","time":1612954993,"author":"Edward Rodriguez","text":"#RRT19282021021","highlighted":true,"type":"user"},"123166539621374":{"id":"123166539621374","time":1612840666,"author":"Katrina Doria","text":"#RRT1918702082021","highlighted":true,"type":"user"},"122817222989639":{"id":"122817222989639","time":1612776230,"author":"Rommel Jaramillo","text":"#RRT2301802082021\n","highlighted":true,"type":"user"},"121465266458168":{"id":"121465266458168","time":1612518220,"author":"Kristine Marie Polvorosa","text":"Used Knowledge Base article [Some Messaging Campaigns and Performance Reporting Unavailable 2454549788184502]","highlighted":true,"type":"system"}}​

{"132228555445717":{"id":"132228555445717","time":1614035621,"author":"Sanilene Cabael","text":"#RRT1988502232021\n#EmailSent","highlighted":true,"type":"user"},"130906055577967":{"id":"130906055577967","time":1613861751,"author":"Sanilene Cabael","text":"#RRT1988502212021\n#EmailSent","highlighted":true,"type":"user"},"130363765632196":{"id":"130363765632196","time":1613779971,"author":"Sanilene Cabael","text":"#RRT1988502202021\n#EmailSent","highlighted":true,"type":"user"},"129931369008769":{"id":"129931369008769","time":1613737427,"author":"Juvy Doyungan","text":"#RRT2483002192021\n","highlighted":true,"type":"user"},"129433622391877":{"id":"129433622391877","time":1613662213,"author":"Paul John Honrubia","text":"#RRT_PaulJohnHonrubia","highlighted":true,"type":"user"},"122633076405265":{"id":"122633076405265","time":1612626402,"author":"Sanilene Cabael","text":"Used Knowledge Base article [Pages Operations: Page Merge: Request Merge 2283563565214561]","highlighted":true,"type":"system"},"122630296405543":{"id":"122630296405543","time":1612626047,"author":"Sanilene Cabael","text":"Used Knowledge Base article [Pages Operations: Access: Disputed Admin 568236683662374]","highlighted":true,"type":"system"}}​

{"122715309662140":{"id":"122715309662140","time":1612496975,"author":"Luisito Noel Magleo","text":"#RRT2106102042021","highlighted":true,"type":"user"},"122656893001315":{"id":"122656893001315","time":1612484761,"author":"Luisito Noel Magleo","text":"#EMAILER2106102042021","highlighted":true,"type":"user"},"122656846334653":{"id":"122656846334653","time":1612484745,"author":"Luisito Noel Magleo","text":"Used Knowledge Base article [Ad Account Admin: Status: Banhammer 642615776308145]","highlighted":true,"type":"system"}}​

 

[Rick Rothstein]

Are you referring to the blank rows that appear in Column B? If so, that is because you grabbed the code I posted originally before I edited it to remove the last N=N+1 (located between the two Next statements) which I included to facilitate my testing (it allowed me to which group of data came from which cells. Remove that line of code (remember, it is the last N=N+1, not the first one) and I think it will work as you expect. Just so you know, no data was missing, it was strictly a row output display problem.

 

[whyjaydee]

Are you referring to the blank rows that appear in Column B? If so, that is because you grabbed the code I posted originally before I edited it to remove the last N=N+1 (located between the two Next statements) which I included to facilitate my testing (it allowed me to which group of data came from which cells. Remove that line of code (remember, it is the last N=N+1, not the first one) and I think it will work as you expect. Just so you know, no data was missing, it was strictly a row output display problem.

Hi Rick,

I was laughing hard. I am so dumb.
Yes that was I am referring to. Did remove the last N=N+1 and no more blanks on column B.

Thank you for all the help! Cheers!

 

[Herakles]

Hi Herakles,

I noticed that some of the hit was not extracted from the pool of characters:
Example pool is below

This is placed in cell A1:
The #RRT2401502112021 is not found in the result which is in column B
(That is just 1 of the example that is missing, not sure if there are others)

Thank you in advance

[/LEFT] Public Sub SubMain() Dim intLastRow As Integer Dim arrData As Variant Dim i As Integer Dim strData As String On Error GoTo Err_Handler ActiveWorkbook.Save With Range("A:A") .WrapText = True .ColumnWidth = 30 .RowHeight = 30 With .Cells(1, 1) .value = "Raw Data" .Interior.Color = RGB(171, 171, 171) .Font.Bold = True End With End With With Range("B:B") .ColumnWidth = 30 .RowHeight = 30 .HorizontalAlignment = xlRight .ClearContents With .Cells(1, 1) .value = "Extract" .HorizontalAlignment = xlLeft .IndentLevel = 1 .Interior.Color = RGB(171, 171, 171) .Font.Bold = True End With End With Range("A2").Select ActiveWindow.FreezePanes = True intLastRow = Cells(Rows.Count, "A").End(xlUp).Row arrData = Range("A2:A" & intLastRow) For i = LBound(arrData) To UBound(arrData) strData = arrData(i, 1) Call subExtractStrings(i, strData) Next i MsgBox "Finished Extracting Strings", vbInformation, "Confirmation" Exit_Handler: Exit Sub Err_Handler: MsgBox Err.Number & " " & Err.Description Resume Exit_Handler End Sub Public Sub subExtractStrings(intSourceRow As Integer, ByVal strText As String) Dim i As Integer Dim strChars As String Dim arrText() As String Dim s As String Dim intNextRow As Integer strText = Replace(strText, "quot", " ") ' Replace these characters with a space. strChars = ":;(){},&\" For i = 1 To Len(strChars) strText = Replace(strText, Mid(strChars, i, 1), " ") Next i ' Replace all double spaces with a single space. Do While InStr(1, strText, " ", vbTextCompare) > 0 strText = Replace(strText, " ", " ", 1) Loop ' Populate an array with one word per element arrText = Split(strText, " ") If Range("B2").value = "" Then intNextRow = 2 Else intNextRow = Cells(Rows.Count, "B").End(xlUp).Row + 1 End If ' Loop through the array and write to worksheet any words that start with '#RRT' ' and end with '2021' For i = LBound(arrText) To UBound(arrText) If Left(arrText(i), 4) = "#RRT" And Right(arrText(i), 4) = "2021" Then Cells(intNextRow, "B") = arrText(i) intNextRow = intNextRow + 1 End If Next i End Sub [LEFT]

This considers the inclusion of the '\' character and the example that you quote is now included. Ricks code is more succinct but needs to include all rows and the inclusion of strings starting with a '#EMAILER'. My code needs a blank first row.

 

[whyjaydee]

This considers the inclusion of the '\' character and the example that you quote is now included. Ricks code is more succinct but needs to include all rows and the inclusion of strings starting with a '#EMAILER'. My code needs a blank first row.

Hi Herakles,

Rick's codes seems to extract strings that starts with #EMAILER as well, sorry what do you mean by your last comment?

 

[Rick Rothstein]

Rick's codes seems to extract strings that starts with #EMAILER as well...

Good point! Thanks for noting it. This code should fix the problem...

Sub SplitPound2021TextFromA1ToB1Down()
  Dim N As Long, X As Long, Cell As Range, Arr1 As Variant, Arr2 As Variant
  Columns("B").Clear
  For Each Cell In Range("A1", Cells(Rows.Count, "A"))
    Arr1 = Split(1, Cell, "#RR", vbTextCompare)
    For X = 1 To UBound(Arr1)
      If Arr1(X) Like "*2021*" Then
        Arr2 = Split(Arr1(X), 2021)
        N = N + 1
        Cells(N, "B") = "#RR" & Arr2(0) & 2021
      End If
    Next
  Next
End Sub

 

[whyjaydee]

Good point! Thanks for noting it. This code should fix the problem...

Sub SplitPound2021TextFromA1ToB1Down()
  Dim N As Long, X As Long, Cell As Range, Arr1 As Variant, Arr2 As Variant
  Columns("B").Clear
  For Each Cell In Range("A1", Cells(Rows.Count, "A"))
    Arr1 = Split(1, Cell, "#RR", vbTextCompare)
    For X = 1 To UBound(Arr1)
      If Arr1(X) Like "*2021*" Then
        Arr2 = Split(Arr1(X), 2021)
        N = N + 1
        Cells(N, "B") = "#RR" & Arr2(0) & 2021
      End If
    Next
  Next
End Sub

Hi Rick,

I am getting this error:

Type mismatch (Error 13)​