if I have the number 5 in cell a1 how can I get 1,2,3,4,6,7,8,9 to appear in
cell c1?
ie 1-9 but not 5
then if there are two numbers in a1 eg 2 and 7 I want 1,3,4,5,6,8,9 to
appear in cell c1
Any suggestions?
--
CHRISK

Hi

=SUBSTITUTE(SUBSTITUTE("1,2,3,4,5,6,7,8,9",A1,""), ",,",",")

But the 2nd part of your question in't so easy to answer anymore. Probably
the best solution will be an UDF.


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



"CHRIS K" wrote in message
...
if I have the number 5 in cell a1 how can I get 1,2,3,4,6,7,8,9 to appear
in
cell c1?
ie 1-9 but not 5
then if there are two numbers in a1 eg 2 and 7 I want 1,3,4,5,6,8,9 to
appear in cell c1
Any suggestions?
--
CHRISK

What's a UDF?
--
CHRISK


"Arvi Laanemets" wrote:

Hi

=SUBSTITUTE(SUBSTITUTE("1,2,3,4,5,6,7,8,9",A1,""), ",,",",")

But the 2nd part of your question in't so easy to answer anymore. Probably
the best solution will be an UDF.


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



"CHRIS K" wrote in message
...
if I have the number 5 in cell a1 how can I get 1,2,3,4,6,7,8,9 to appear
in
cell c1?
ie 1-9 but not 5
then if there are two numbers in a1 eg 2 and 7 I want 1,3,4,5,6,8,9 to
appear in cell c1
Any suggestions?
--
CHRISK

Hi


"CHRIS K" wrote in message
...
What's a UDF?


User Defined Function


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )

HOW do you do a UDF?
--
CHRISK


"Arvi Laanemets" wrote:

Hi


"CHRIS K" wrote in message
...
What's a UDF?


User Defined Function


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )

With workbook open:
press Alt+F11
InsertModule (when you don't have one)
InsertProcedure, type in the function name, set Type to Function and press
OK
A dummy function is created. Fill it with code, and close VBA editor.
When you now activate Paste Function wizard in Excel, you can find created
UDF under category User Defined.

Some rules you have to follow:
By definition a function can't change anything - it only returns a value. So
you have to avoid according statements, like Select, Activate, etc.
To return a value, you have to save it to variable with same name as
function.

An example:
Public Sub Test(TestString As String) As String
Test=TestString
End Sub


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



"CHRIS K" wrote in message
...
HOW do you do a UDF?
--
CHRISK


"Arvi Laanemets" wrote:

Hi


"CHRIS K" wrote in message
...
What's a UDF?


User Defined Function


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )