String of Numbers

K

KLock

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

Ron Rosenfeld

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,
Click to expand...


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
 
S

Sheeloo

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
 
S

Shane Devenshire

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.
 
J

Joel

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
 
Top