Solved

Removing text between BBCODE tags in Excel

Posted on 2016-10-17
3
105 Views
Last Modified: 2016-10-19
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!
0
Comment
Question by:Iago Varela
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 18

Expert Comment

by:xtermie
ID: 41847852
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
 
LVL 40

Accepted Solution

by:
als315 earned 500 total points
ID: 41848902
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
 

Author Closing Comment

by:Iago Varela
ID: 41849839
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

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question