Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

WaelYassin

Technical User
Jul 9, 2006
65
EG
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
 
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 ".|".
 

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...
Code:
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]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Thanks all of you,
But Special one for Skip, I amended the last line in the function to be
Code:
MakeList = Left(MakeList, Len(MakeList) - Len(CM))
This will make the # of truncated characters dynamic to the delimenter's characters count.

Thanks again
 



Should have been...
Code:
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]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
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.
 



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

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top