[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Removing text between BBCODE tags in Excel

Posted on 2016-10-17
3
Medium Priority
?
142 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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

656 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