Removing text between BBCODE tags in Excel

Hi all. I have excel 2013, where I'm using PowerQuery to get data from a forum DB, and later count the number of words for each user and for each month, with pivot tables.

The problem is that I get messages like this one:

=================CELL=================

[quote='Pande' pid='38' dateline='1475615784']
[quote='Jotipleistus' pid='37' dateline='1475611270']

QUOTE 2 TEXT

[/quote]

QUOTE 1 TEXT

[/quote]

ACTUAL USER TEXT

[quote='Pande' pid='38' dateline='1475615784']
[quote='Jotipleistus' pid='37' dateline='1475611270']

QUOTE 4 TEXT

[/quote]

QUOTE 3 TEXT

[/quote]

ACTUAL USER TEXT 2

=================/CELL=================

Typically, with static data I would just user search and replace with a wildcard like [quote*quote] to remove it all, so only the user typed text remain, because I only want to count those words, not quoted ones.

But in this case I need to do it automatically, so a cell next to the original message contains only the words weitten by the user, without quotes. I already tried with buil-in excel formulas, but I can just replace the first quote in the best scenario. I also tried searching on the Internet for VBA but nothing really worked for me. I guess I'm really pushing the boundaries of what Im capable for, so maybe I should learn programming... but that will take me a while.

So, does anybody know any solution to be applied inside Excel?

Thank you in advance!
Iago VarelaOrchestra-manAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

xtermieCommented:
In Excel you can run (or set to automatically run on open or on close of the spreadsheet) a simple macro to find and replace (what you would do manually)
Try this:
Sub testreplace()
Dim x As String, y As String
y = InputBox("Replace what?")  'this will ask what you want to replace, i.e. quote*quote
x = InputBox("Replace to?")       'this will ask what will the replacement be, i.e. "" (blank)
    [A1:E100].Replace what:=y, replacement:=x    'Set the range you want instead of A1:E100
End Sub   

Open in new window

0
als315Commented:
It is not so simple, because all text between quote tags should be removed.
You can use function:
Public Function remove_q(R As Range, OTAG As String, CTAG As String) As String
Dim S As String, RS As String, L1 As Long, L2 As Long, Q As Integer
' R - reference to cell, OTAG - opening tag ([quote), CTAG - closing tag ([/quote])
RS = R.Value
Q = 0
L1 = InStr(1, RS, OTAG, vbBinaryCompare)
L2 = InStr(1, RS, CTAG, vbBinaryCompare)
S = Left(RS, L1 - 1)
Do While L1 > 0 Or L2 > 0
  If L2 > L1 And L1 > 0 Then
    Q = Q + 1
  Else
    Q = Q - 1
    L1 = L2 + 8
    If L1 > Len(RS) Then L1 = Len(RS) - 1
  End If
  If Q = 0 Then
    RS = Mid(RS, L2 + 8)
    L1 = InStr(1, RS, OTAG, vbBinaryCompare)
    If L1 > 0 Then
        S = S & Left(RS, L1 - 1)
        L1 = 0
        L2 = 0
    Else
        S = S & RS
        Exit Do
    End If
  End If
  L2 = InStr(L1 + 1, RS, CTAG, vbBinaryCompare)
  L1 = InStr(L1 + 1, RS, OTAG, vbBinaryCompare)
Loop
remove_q = S
End Function

Open in new window

usage:
=remove_q(A1, "[quote", "[/quote]")
Remove_tags.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Iago VarelaOrchestra-manAuthor Commented:
Wow, thank you very much! It works perfectly. Hope you have a nice day, love, sex and tax cuts because of being a great guy! haha
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.