1

I have text in a range of cells like

Manufacturer#||#Coaster#|#|Width (side to side)#||#20" W####Height (bottom to top)#||#35" H#|#|Depth (front to back)#||#20.5" D####Seat Depth#||#14.25"**#|#|Material & Finish####**Composition#||#Wood Veneers & Solids#|#|Composition#||#Metal#|#|Style Elements####Style#||#Contemporary#|#|Style#||#Casual

From this cell i need to remove strings between #|#|"needtoremove"#### only without affecting other strings. I have tried find and replace, finding #|#|*#### and replacing it with #|#|. However its not giving the exact result.

Can anyone help me?

Community
  • 1
  • 1
Linga
  • 945
  • 1
  • 14
  • 31
  • If you post the specifics of what you've tried, you're more likely to have someone help you fix it, as opposed to having it written for you. – FreeMan Jul 08 '15 at 16:08
  • There are so many `#|#|` and `####` Which of the values are you looking for? – Siddharth Rout Jul 08 '15 at 16:34
  • i Should remove all the strings between #|#| and #### but not between #|#| and #|#| and #### – Linga Jul 08 '15 at 17:45

2 Answers2

1

The other solution will remove anything between the first #|#| and ####, event the #||# etc.

In case you only need to remove the text between #|#| and #### only if there is no other ##|| inbetween, I think the simplest way is to use a regex.

You will need to activate the Microsoft VBScript Regular Expressions 5.5 library in Tools->References from the VBA editor.

Change range("D166") to wherever your cell is. The expression as it is right now ("#\|#\|[A-Za-z0-9& ]*####")matches any text that starts with #|#|, ends with #### and has any number of alphanumerical character, & or space. You can add other caracters between the brakets if needed.

Sub remove()
Dim reg As New RegExp
Dim pattern As String
Dim replace As String
Dim strInput As String
strInput = Range("D166").Value
replace = ""
pattern = "#\|#\|[A-Za-z0-9& ]*####"
With reg
    .Global = True
    .MultiLine = True
    .IgnoreCase = False
    .pattern = pattern
End With


If reg.test(strInput) Then Range("D166").Value = reg.replace(strInput, replace)

End Sub
Latch
  • 368
  • 1
  • 9
  • I got error on "Dim reg As New RegExp" should i do? – Linga Jul 08 '15 at 17:46
  • 1
    Did you add the reference to the Microsoft VBScript Regular Expressions 5.5? Here is a step by step on doing so: http://stackoverflow.com/a/22542835/5042442 You only need to do the step 1 steps. – Latch Jul 08 '15 at 18:06
  • Sry i know English only that link has some other language – Linga Jul 08 '15 at 18:15
  • This link is definitely english... [Here is the msdn entry](https://msdn.microsoft.com/en-us/library/office/gg264402.aspx) on adding a reference. You can change the language at the bottom left if it's easier to you. Then you need to find Microsoft VBScript Regular Expressions 5.5 in the window that opens. – Latch Jul 08 '15 at 18:42
  • Hi Latch, thanks for helping :) Finally my source has been updated. – Linga Jul 10 '15 at 15:37
0

Something like this.

If that value is in cell A1

Dim str As String
Dim i As Integer
Dim i2 As Integer
Dim ws As Excel.Worksheet

Set ws = Application.ActiveSheet

str = ws.Range("A1").Value
i = InStr(str, "#|#|")
i2 = InStr(str, "####")

str = Left(str, i) & Right(str, Len(str) - i2)

ws.Range("A1").Value = str
MatthewD
  • 6,719
  • 5
  • 22
  • 41
  • I added the line at the bottom to write the modified string in the variable back to the spreadsheet. As is stated this is just modifying cell A1. If you need a loop example i can provide it. – MatthewD Jul 08 '15 at 17:54
  • ws needs to be your worksheet. I will add that for you. – MatthewD Jul 08 '15 at 18:40
  • Hi MathewD, thanks for helping :) Finally my source has been updated. – Linga Jul 10 '15 at 15:38