VBA code, to remove tab and new line

[drjpb]

Hi,

It's been a while I have posted a question here, but when I had this issue, first thing came to my mind was 'MrExcel.com'

Anyways, I use outlook to search multiple items (invoices) and i make use of keywords.
let's say I am looking for 3 items, 111236-2, 111235, 111321. Usually the list consists of 50+ invoices, some with dash1, dash2, etc, so the technique I use is, make a list of invoices in Excel spread sheet, and add double quote into the cell before the invoice, and again double quote in the cell after invoice, followed by : OR " i.e.:

"	111236-2	" OR "
	111235​	" OR "
	111321​	" OR "

Next step, I copy and paste it in word file as values. this gives me the option to use replace function, so I use Replace: ^p with blank (replacing linebreak) and again Replace : ^t with blank (replacing tab space), and my list is ready to be pasted in outlook search:

"111236-2" OR "111235" OR "111321" OR "

I just manually remove the extra last OR ", giving me final :

"111236-2" OR "111235" OR "111321"

Can this be done using VBA?

Thanks in advance.

 

[Fluff]

You can do that with a formula

Fluff.xlsm
	A	B
1
2	111236-2	"111236-2" OR "111235" OR "111321"
3	111235
4	111321
5
Data

Cell Formulas
Range	Formula
B2	B2	=""""&TEXTJOIN(""" OR """,,A2:A10)&""""

 

[drjpb]

Thank you @Fluff , I am surprised that this didn't clicked me before, a simple formula can do the trick.

However, I want this for my team, and most of the team are seniors, who usually struggle with these formulas and multiple steps manual cleansing, can this be done with VBA Code, I want to provide them with a spread sheet, where they copy paste the list and click on the provided VBA button, which prepares it and copy the results into clipboard.

Thank you.

 

[Cubist]

Try this:

Sub ConcatenateWithOR()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim result As String
    Dim delimiter As String
    Dim outputCell As Range
    Dim lastRow As Long
   
    Set ws = ThisWorkbook.ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Set rng = ws.Range("A1:A" & lastRow) 'Assumes your data starts from A1
    Set outputCell = ws.Range("B1") 'Outputting to B1
    delimiter = " OR "
   
    For Each cell In rng
        If cell.Value <> "" Then
            If result = "" Then
                result = """" & cell.Value & """"
            Else
                result = result & delimiter & """" & cell.Value & """"
            End If
        End If
    Next cell
   
    outputCell.Value = result
End Sub

 

[drjpb]

Try this:

Sub ConcatenateWithOR()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim result As String
    Dim delimiter As String
    Dim outputCell As Range
    Dim lastRow As Long
  
    Set ws = ThisWorkbook.ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    Set rng = ws.Range("A1:A" & lastRow) 'Assumes your data starts from A1
    Set outputCell = ws.Range("B1") 'Outputting to B1
    delimiter = " OR "
  
    For Each cell In rng
        If cell.Value <> "" Then
            If result = "" Then
                result = """" & cell.Value & """"
            Else
                result = result & delimiter & """" & cell.Value & """"
            End If
        End If
    Next cell
  
    outputCell.Value = result
End Sub

@Cubist , this is great, I believe I will go with this one.

Thank you @Cubist & @Fluff for your time & efforts, I will add the copy code at the end so that once it's prepared in B2, it will copy the cell content in advance for the users,

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[drjpb]

Glad we could help & thanks for the feedback.

It's been a while i posted on this fourm, is it okay to mark both as solutions? IMO both works well and both deserve to be marked as Solution.

Please advise.

 

[drjpb]

Nevermind, I was going to tag your post as solution as well, but I learned that we can tag only one as Solution.

Thanks.

 

[Fluff]

Only one post can be marked as the solution, so I would leave it as Cubists's