CODEWITHSUNDEEP
excelexcel-formulavba
Naveen Chand K
Naveen Chand K

Reputation: 426

Remove text appearing between two characters - multiple instances - Excel

In Microsoft Excel file, I have a text in rows that appears like this:

1. Rc8 {[%emt 0:00:05]} Rxc8 {[%emt 0:00:01]} 2. Rxc8 {[%emt 0:00:01]} Qxc8 {} 3. Qe7#  1-0

I need to remove any text appearing within the flower brackets { and }, including the brackets themselves.

In the above example, there are three instances of such flower brackets. But some rows might have more than that.

I tried =MID(LEFT(A2,FIND("}",A2)-1),FIND("{",A2)+1,LEN(A2))

This outputs to: {[%emt 0:00:05]}. As you see this is the very first instance of text between those flower brackets.

And if we use this to within SUBSTITUTE like this: =SUBSTITUTE(A2,MID(LEFT(A2,FIND("}",A2)),FIND("{",A2),LEN(A2)),"")

I get an output like this:

1. Rc8 Rxc8 {[%emt 0:00:01]} 2. Rxc8 {[%emt 0:00:01]} Qxc8 {} 3. Qe7# 1-0

If you have noticed, only one instance is removed. How do I make it work for all instances? thanks.

Upvotes: 2

Views: 20090

Answers (3)

user7874411
user7874411

Reputation: 51

Highlight everything Go to replace enter {*} in text to replace leave replace with blank

This should replace all flower brackets and anything in between them

Upvotes: 5

D_Bester
D_Bester

Reputation: 5901

Try a user defined function. In VBA create a reference to "Microsoft VBScript Regular Expressions 5.5. Then add this code in a module.

Function RemoveTags(ByVal Value As String) As String
    Dim rx As New RegExp
    rx.Global = True
    rx.Pattern = " ?{.*?}"
    RemoveTags = Trim(rx.Replace(Value, ""))
End Function

On the worksheet in the cell enter: =RemoveTags(A1) or whatever the address is where you want to remove text.

If you want to test it in VBA:

Sub test()
    Dim a As String
    a = "Rc8 {[%emt 0:00:05]} Rxc8 {[%emt 0:00:01]}"
    Debug.Print RemoveTags(a)
End Sub

Outputs "Rc8 Rxc8"

Upvotes: 1

Dirk Reichel
Dirk Reichel

Reputation: 7979

It is not that easy without VBA, but there is still a way.

Either (as suggested by yu_ominae) just use a formula like this and auto-fill it:

=IFERROR(SUBSTITUTE(A2,MID(LEFT(A2,FIND("}",A2)),FIND("{",A2),LEN(A2)),""),A2)

Another way would be iterative calculations (go to options -> formulas -> check the "enable iterative calculations" button)
To do it now in one cell, you need 1 helper-cell (for my example we will use C1) and the use a formula like this in B2 and auto-fill down:

=IF($C$1,A2,IFERROR(SUBSTITUTE(B2,MID(LEFT(B2,FIND("}",B2)),FIND("{",B2),LEN(B2)),""),B2))

Put "1" in C1 and all formulas in B:B will show the values of A:A. Now go to C1 and hit the del-key several times (you will see the "{}"-parts disappearing) till all looks like you want it.

EDIT: To do it via VBA but without regex you can simply put this into a module:

Public Function DELBRC(ByVal str As String) As String
  While InStr(str, "{") > 0 And InStr(str, "}") > InStr(str, "{")
    str = Left(str, InStr(str, "{") - 1) & Mid(str, InStr(str, "}") + 1)
  Wend
  DELBRC = Trim(str)
End Function

and then in the worksheet directly use:

=DELBRC(A2)

If you still have any questions, just ask ;)

Upvotes: 3

Related Questions