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,

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

Suppose your numbers are in Col A starting at A1
enter this in B1
="'" & A1& "'"

enter this in B2
=IF(A2="",B1, B1 & ",'" & A2 & "'")

Copy this down beyond the maximum numbers you expect... and last cell in Col
B will give you the string

"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,

Hi,

One thing to keep in mind in 2003 - the maximumn number of characters in a
formula is 1024 so if your string of numbers is long you may bump up against
this limit.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"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,

The best way is with a UDF function

Call function with following from worksheet
= JoinF(A1:A10) or any number of cells.

I didn't include the equal sign because I wasn't sure if that was part of
the format. It is easy to add the equal sign if necessary.


Function JoinF(target As Range) As String

JoinF = ""
For Each cell In target
If JoinF = "" Then
JoinF = "'" & cell & "'"
Else
JoinF = JoinF & ",'" & cell & "'"
End If

Next cell

End Function

"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,