Use cells data in Ecel sheet to create a search string 1

[WaelYassin]

Dear All,
I am trying to use excel sheet to do the following, i have a set of strings that can be output from other program, i want to paste them in Excel then go through each cell contents and concatenate it with (.|) then the next cell contents.

Example:

Brown
Yellow
Red
Black


Output should be: Brown.|Yellow.|Red.|Black

 

[soccercook44]

If your output is already in excel then, just set up a temp string variable as your output.

Then loop through the cells and concatinate them to the temp adding in your ".|".

 

[SkipVought]

Hi,

I'll share this handy function that I use frequently. You can use it on a sheet, like any other spreadsheet function as well. Paste into a MODULE.

In your case, call as such, gets you 90% there...

Function MakeList(rng As Range, Optional TK As String = "'", Optional CM As String = ",") As String
'SkipVought/2005 Jun 13/
'--------------------------------------------------
' Access: N/A
'--------------------------------------------------
':this function returns a single-quoted list that can be used, for instance _
in an IN Clause in SQL _
"WHERE PART_ID IN (" & MakeList([SomeRange]) & ")"
'--------------------------------------------------
    Dim r As Range
    
    For Each r In rng.SpecialCells(xlCellTypeVisible)
        With r
            If Not .EntireRow.Hidden Then _
                MakeList = MakeList & TK & Trim(.Value) & TK & CM
        End With
    Next
    MakeList = Left(MakeList, Len(MakeList) - 1)
End Function

All you have to do is truncate the last character.
My Call...
[tt]
=LEFT(MakeList(A3:A6,"",".|"),LEN(MakeList(A3:A6,"",".|"))-1)
[/tt]
My result...
[tt]
Brown.|Yellow.|Red.|Black
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[WaelYassin]

Thanks all of you,
But Special one for Skip, I amended the last line in the function to be

MakeList = Left(MakeList, Len(MakeList) - Len(CM))

This will make the # of truncated characters dynamic to the delimenter's characters count.

Thanks again

 

[SkipVought]

Should have been...

Function MakeList(rng As Range, Optional TK As String = "'", Optional CM As String = ",") As String
'SkipVought/2005 Jun 13/
'--------------------------------------------------
' Access: N/A
'--------------------------------------------------
':this function returns a single-quoted list that can be used, for instance _
in an IN Clause in SQL _
"WHERE PART_ID IN (" & MakeList([SomeRange]) & ")"
'--------------------------------------------------
    Dim r As Range
    
    For Each r In rng.SpecialCells(xlCellTypeVisible)
        With r
            If Not .EntireRow.Hidden Then _
                MakeList = MakeList & TK & Trim(.Value) & TK & CM
        End With
    Next
    MakeList = Left(MakeList, Len(MakeList) - [b]Len(TK)[/b])
End Function

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[WaelYassin]

No, you are concatenating with the (CM) in advance, not the token, so if you got the last value, no need for the last (CM).

As in My Example, when you substitute in your good Function Output will be: Brown.|Yellow.|Red.|Black.|

Remember that i neglected the last line in the code to show the difference, but if you used the last line, it will be:
Brown.|Yellow.|Red.|Black

The reason is to make the number of characters cut at the end of the output dynamic, no matter how many CM characters you use.

 

[SkipVought]

You are correct. Substitute the CM for the TK in that last statement.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]