Solved

Removing text between BBCODE tags in Excel

Posted on 2016-10-17
3
74 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 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
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.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

839 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