Hello,
I have a procedure that perform often that I would like to convert into a
function called "DASHES". All it does is take a 10-digit number in a cell and
insert dashes into the appropriate places, and save the result. I did a macro
to get the code, but I don't know how to turn this into a function that will
be available every time I use my copy of Excel. Here is the code:

Sub DASHES()
'
' DASHES Macro
' Macro recorded 10/15/2008 by Dino
'
ActiveCell.FormulaR1C1 = _
"=LEFT(RC[-1],4)&""-""&MID(RC[-1],5,3)&""-""&RIGHT(RC[-1],3)"
Range("C3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub


Any help would be appreciated, thanks!

What you are asking for can be done but there are some drawbacks... You need
to put the code in an addin. The addin will only exist on your mahine so if
you send the file to anyone else the function will bomb... A better option in
my opinioin would be to use a custom format...

Format -> Format Cell -> Number -> Custom 000-000-0000

The format will be embeded in the cell and it is very easy to do...
--
HTH...

Jim Thomlinson


"Dino" wrote:

> Hello,
> I have a procedure that perform often that I would like to convert into a
> function called "DASHES". All it does is take a 10-digit number in a cell and
> insert dashes into the appropriate places, and save the result. I did a macro
> to get the code, but I don't know how to turn this into a function that will
> be available every time I use my copy of Excel. Here is the code:
>
> Sub DASHES()
> '
> ' DASHES Macro
> ' Macro recorded 10/15/2008 by Dino
> '
> ActiveCell.FormulaR1C1 = _
> "=LEFT(RC[-1],4)&""-""&MID(RC[-1],5,3)&""-""&RIGHT(RC[-1],3)"
> Range("C3").Select
> Selection.Copy
> Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
> False, Transpose:=False
> End Sub
>
>
> Any help would be appreciated, thanks!
>

You did say function. So here is a UDF. Place in a REGULAR module>on the ws
type =dashes(1111111111) to see what you get.

Function dashes(x)
dashes = Left(x, 4) & "-" & Mid(x, 5, 3) & "-" & Right(x, 3)
End Function

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Dino" <(E-Mail Removed)> wrote in message
news:7DB5E435-4054-4F30-AEFD-(E-Mail Removed)...
> Hello,
> I have a procedure that perform often that I would like to convert into a
> function called "DASHES". All it does is take a 10-digit number in a cell
> and
> insert dashes into the appropriate places, and save the result. I did a
> macro
> to get the code, but I don't know how to turn this into a function that
> will
> be available every time I use my copy of Excel. Here is the code:
>
> Sub DASHES()
> '
> ' DASHES Macro
> ' Macro recorded 10/15/2008 by Dino
> '
> ActiveCell.FormulaR1C1 = _
> "=LEFT(RC[-1],4)&""-""&MID(RC[-1],5,3)&""-""&RIGHT(RC[-1],3)"
> Range("C3").Select
> Selection.Copy
> Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
> _
> False, Transpose:=False
> End Sub
>
>
> Any help would be appreciated, thanks!
>

Thanks that worked great!


"Don Guillett" wrote:

> You did say function. So here is a UDF. Place in a REGULAR module>on the ws
> type =dashes(1111111111) to see what you get.
>
> Function dashes(x)
> dashes = Left(x, 4) & "-" & Mid(x, 5, 3) & "-" & Right(x, 3)
> End Function
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "Dino" <(E-Mail Removed)> wrote in message
> news:7DB5E435-4054-4F30-AEFD-(E-Mail Removed)...
> > Hello,
> > I have a procedure that perform often that I would like to convert into a
> > function called "DASHES". All it does is take a 10-digit number in a cell
> > and
> > insert dashes into the appropriate places, and save the result. I did a
> > macro
> > to get the code, but I don't know how to turn this into a function that
> > will
> > be available every time I use my copy of Excel. Here is the code:
> >
> > Sub DASHES()
> > '
> > ' DASHES Macro
> > ' Macro recorded 10/15/2008 by Dino
> > '
> > ActiveCell.FormulaR1C1 = _
> > "=LEFT(RC[-1],4)&""-""&MID(RC[-1],5,3)&""-""&RIGHT(RC[-1],3)"
> > Range("C3").Select
> > Selection.Copy
> > Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
> > _
> > False, Transpose:=False
> > End Sub
> >
> >
> > Any help would be appreciated, thanks!
> >
>
>

Thanks! Is there a way to save this function so that it'll work every time I
use Excel regardless of what spreadsheet I'm opening? Or do I have to copy it
into every spreadsheet that I intend to use it?


"Don Guillett" wrote:

> You did say function. So here is a UDF. Place in a REGULAR module>on the ws
> type =dashes(1111111111) to see what you get.
>
> Function dashes(x)
> dashes = Left(x, 4) & "-" & Mid(x, 5, 3) & "-" & Right(x, 3)
> End Function
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "Dino" <(E-Mail Removed)> wrote in message
> news:7DB5E435-4054-4F30-AEFD-(E-Mail Removed)...
> > Hello,
> > I have a procedure that perform often that I would like to convert into a
> > function called "DASHES". All it does is take a 10-digit number in a cell
> > and
> > insert dashes into the appropriate places, and save the result. I did a
> > macro
> > to get the code, but I don't know how to turn this into a function that
> > will
> > be available every time I use my copy of Excel. Here is the code:
> >
> > Sub DASHES()
> > '
> > ' DASHES Macro
> > ' Macro recorded 10/15/2008 by Dino
> > '
> > ActiveCell.FormulaR1C1 = _
> > "=LEFT(RC[-1],4)&""-""&MID(RC[-1],5,3)&""-""&RIGHT(RC[-1],3)"
> > Range("C3").Select
> > Selection.Copy
> > Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
> > _
> > False, Transpose:=False
> > End Sub
> >
> >
> > Any help would be appreciated, thanks!
> >
>
>

If you save a workbook as an add-in with that function in a module.

Or if you store it in your Personal.xls.

In that case you would enter it as =Personal.xls!dashes(cellref) or
dashes(10-digit number)

If stored in an add-in you don't need the filename!


Gord Dibben MS Excel MVP

On Wed, 15 Oct 2008 16:34:01 -0700, Dino <(E-Mail Removed)>
wrote:

>Thanks! Is there a way to save this function so that it'll work every time I
>use Excel regardless of what spreadsheet I'm opening? Or do I have to copy it
>into every spreadsheet that I intend to use it?
>
>
>"Don Guillett" wrote:
>
>> You did say function. So here is a UDF. Place in a REGULAR module>on the ws
>> type =dashes(1111111111) to see what you get.
>>
>> Function dashes(x)
>> dashes = Left(x, 4) & "-" & Mid(x, 5, 3) & "-" & Right(x, 3)
>> End Function
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "Dino" <(E-Mail Removed)> wrote in message
>> news:7DB5E435-4054-4F30-AEFD-(E-Mail Removed)...
>> > Hello,
>> > I have a procedure that perform often that I would like to convert into a
>> > function called "DASHES". All it does is take a 10-digit number in a cell
>> > and
>> > insert dashes into the appropriate places, and save the result. I did a
>> > macro
>> > to get the code, but I don't know how to turn this into a function that
>> > will
>> > be available every time I use my copy of Excel. Here is the code:
>> >
>> > Sub DASHES()
>> > '
>> > ' DASHES Macro
>> > ' Macro recorded 10/15/2008 by Dino
>> > '
>> > ActiveCell.FormulaR1C1 = _
>> > "=LEFT(RC[-1],4)&""-""&MID(RC[-1],5,3)&""-""&RIGHT(RC[-1],3)"
>> > Range("C3").Select
>> > Selection.Copy
>> > Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
>> > _
>> > False, Transpose:=False
>> > End Sub
>> >
>> >
>> > Any help would be appreciated, thanks!
>> >
>>
>>