Create a string from a list 1

[greengo204]

I am trying to create a a single string from a list of values.

e.g.

Col A

A/C CODE
05FFF564
06DDD555
07YYY222
08UUU333
09III222

Desired output (Single String):

['05FFF564','06DDD555','07YYY222','08UUU333','09III222']

I've got basic knowledge of VBA and not sure where to start on this task.

Any help or direction would be greatly appreciated

 

[SkipVought]

Hi,

Here's one that I use regularly for IN clauses in SQL 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
        With r
            MakeList = MakeList & TK & Trim(.Value) & TK & CM
        End With
    Next
    MakeList = Left(MakeList, Len(MakeList) - 1)
End Function

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[HarleyQuinn]

Is this in Excel?

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[greengo204]

HarleyQuinn:

Using Excel 2003

SkipVought:

I need it for the same reason "regularly for IN clauses in SQL code..."

Works perfect

Thanks