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

whyjaydee

New Member
Joined
Feb 26, 2021
Messages
19
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,975
Office Version
  1. 2016
Platform
  1. Windows
I missed that you were going to have multiple cells in Column A. Give this a try then...
VBA Code:
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

whyjaydee

New Member
Joined
Feb 26, 2021
Messages
19
Office Version
  1. 2016
Platform
  1. Windows
I missed that you were going to have multiple cells in Column A. Give this a try then...
VBA Code:
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:

1614563201811.png


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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,975
Office Version
  1. 2016
Platform
  1. Windows
Can you post those first 10 cell values so I can copy/paste them into an Excel worksheet (I cannot work with a picture)?
 
Last edited:

whyjaydee

New Member
Joined
Feb 26, 2021
Messages
19
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,975
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Feb 26, 2021
Messages
19
Office Version
  1. 2016
Platform
  1. Windows
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

Board Regular
Joined
Jul 5, 2020
Messages
73
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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


VBA Code:
[/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

New Member
Joined
Feb 26, 2021
Messages
19
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,975
Office Version
  1. 2016
Platform
  1. Windows
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...
VBA Code:
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

New Member
Joined
Feb 26, 2021
Messages
19
Office Version
  1. 2016
Platform
  1. Windows
Good point! Thanks for noting it. This code should fix the problem...
VBA Code:
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)​

 

Watch MrExcel Video

Forum statistics

Threads
1,129,574
Messages
5,637,162
Members
416,959
Latest member
Mohzein

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top