On Mon, 29 Dec 2008 14:08:02 -0800, KLock
wrote:

I have a list of numbers;
For example
23489
52356
23563

I want to change into this precise format = '23489','52356','23563'

However CONCATENATE function will not help because list of number can very
in length from one week to another.

Does anybody have an idea of how to create a tool in either excel or access
to give me that precise format no matter how long my list of numbers is?

Thanks for any help,


Here is a UDF (user defined function) that should do that:

========================
Option Explicit
Function ConcatRange(rg As Range)
Dim d() As String
Dim i As Long
ReDim d(rg.Count - 1)

For i = 0 To UBound(d)
d(i) = "'" & rg(i + 1).Text & "'"
Next i

ConcatRange = Join(d, ",")

End Function
=============================

The formula would be =ConcatRange(range_reference)

Range_reference might be A1:A10

To enter this, alt-F11 opens the VBEditor. Ensure your project is highlighted
in the project explorer window, then Insert/Module and paste the code above
into the window that opens.

The UDF does not take into account blank entries, but could be modified to
handle that, if you wish.
--ron