Solved

Removing text between BBCODE tags in Excel

Posted on 2016-10-17
3
57 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
3 Comments
 
LVL 17

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 39

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

911 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now