Prev: Countif function to show how often numbers within a range are pres
Next: GETPIVOTDATE Funtion
From: Gord Dibben on 30 Mar 2010 17:45
Save what?

Just the formula for future use?

I keep a workbook handy where I stick all such stuff with an explanation of
what it does.

Several worksheets.......Indirect, Sumproduct, Vlookup

Copy the formula and paste it.

Precede it with an apostrophe so's it is visible as text.

I keep that workbook always open for testing.

I also store macros and code in several modules named appropriately in an
add-in which is always loaded for testing code.


Gord Dibben MS Excel MVP

On Tue, 30 Mar 2010 14:12:02 -0700, MrMike
<MrMike(a)discussions.microsoft.com> wrote:

>One more thing, is there anyway to save this in excel so I can get to it
>quickly without having to refer to any notes?
>
>
>"David Biddulph" wrote:
>
>> Isn't it as simple as changing
>> =TEXT(SUBSTITUTE(A1,"-",""),"(###) ###-####")
>> to
>> =TEXT(RIGHT(SUBSTITUTE(A1,"-",""),10),"(###) ###-####") ?
>>
>> Why wouldn't that work?
>> --
>> David Biddulph
>>
>>
>> "MrMike" <MrMike(a)discussions.microsoft.com> wrote in message
>> news:B28C8048-E83D-4615-8CDA-B11F2A2CFD17(a)microsoft.com...
>> > This is also a good start, which is more of what I'm looking for, however
>> > I
>> > now have a 4 digit area code because of the on before the number. Is
>> > there a
>> > way to do this and remove just the 1 before the before number? All the
>> > other
>> > functions I've tried removes all the ones in the phone number which is not
>> > what I want to do.
>> >
>> > "Bob Phillips" wrote:
>> >
>> >> Try
>> >>
>> >> =TEXT(SUBSTITUTE(A1,"-",""),"(###) ###-####")
>> >>
>> >> --
>> >>
>> >> HTH
>> >>
>> >> Bob
>> >>
>> >> "MrMike" <MrMike(a)discussions.microsoft.com> wrote in message
>> >> news:2B343330-00F7-4DE6-A6F8-9252C12C6630(a)microsoft.com...
>> >> >I have phone numbers in two different formats:
>> >> > 1. 1-123-456-7890 (11 digits w/dashes)
>> >> > 2. 1234567890 (10 digits only)
>> >> >
>> >> > How can I convert the first format into the standard format in excel
>> >> > for
>> >> > phone numbers of (###) ###-####?
>> >> >
>> >> > I know the second format will do it automatically.
>> >> >
>> >> > Thanks in advance for your help.
>> >>
>> >>
>> >> .
>> >>
>>
>> .
>>

From: Bob Phillips on 30 Mar 2010 19:07
I use the code librarian that came with Excel 2000 Developer, but I always
have trouble re-installing it when I rebuild, so I am thinking of going with
KeyNote.

--

HTH

Bob

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:upr4r5hvelahdblbc7gfkqnfpnnqvj7fm4(a)4ax.com...
> Save what?
>
> Just the formula for future use?
>
> I keep a workbook handy where I stick all such stuff with an explanation
> of
> what it does.
>
> Several worksheets.......Indirect, Sumproduct, Vlookup
>
> Copy the formula and paste it.
>
> Precede it with an apostrophe so's it is visible as text.
>
> I keep that workbook always open for testing.
>
> I also store macros and code in several modules named appropriately in an
> add-in which is always loaded for testing code.
>
>
> Gord Dibben MS Excel MVP
>
> On Tue, 30 Mar 2010 14:12:02 -0700, MrMike
> <MrMike(a)discussions.microsoft.com> wrote:
>
>>One more thing, is there anyway to save this in excel so I can get to it
>>quickly without having to refer to any notes?
>>
>>
>>"David Biddulph" wrote:
>>
>>> Isn't it as simple as changing
>>> =TEXT(SUBSTITUTE(A1,"-",""),"(###) ###-####")
>>> to
>>> =TEXT(RIGHT(SUBSTITUTE(A1,"-",""),10),"(###) ###-####") ?
>>>
>>> Why wouldn't that work?
>>> --
>>> David Biddulph
>>>
>>>
>>> "MrMike" <MrMike(a)discussions.microsoft.com> wrote in message
>>> news:B28C8048-E83D-4615-8CDA-B11F2A2CFD17(a)microsoft.com...
>>> > This is also a good start, which is more of what I'm looking for,
>>> > however
>>> > I
>>> > now have a 4 digit area code because of the on before the number. Is
>>> > there a
>>> > way to do this and remove just the 1 before the before number? All
>>> > the
>>> > other
>>> > functions I've tried removes all the ones in the phone number which is
>>> > not
>>> > what I want to do.
>>> >
>>> > "Bob Phillips" wrote:
>>> >
>>> >> Try
>>> >>
>>> >> =TEXT(SUBSTITUTE(A1,"-",""),"(###) ###-####")
>>> >>
>>> >> --
>>> >>
>>> >> HTH
>>> >>
>>> >> Bob
>>> >>
>>> >> "MrMike" <MrMike(a)discussions.microsoft.com> wrote in message
>>> >> news:2B343330-00F7-4DE6-A6F8-9252C12C6630(a)microsoft.com...
>>> >> >I have phone numbers in two different formats:
>>> >> > 1. 1-123-456-7890 (11 digits w/dashes)
>>> >> > 2. 1234567890 (10 digits only)
>>> >> >
>>> >> > How can I convert the first format into the standard format in
>>> >> > excel
>>> >> > for
>>> >> > phone numbers of (###) ###-####?
>>> >> >
>>> >> > I know the second format will do it automatically.
>>> >> >
>>> >> > Thanks in advance for your help.
>>> >>
>>> >>
>>> >> .
>>> >>
>>>
>>> .
>>>
>


From: MrMike on 31 Mar 2010 11:58
Bob,
I don't see a code librarian in Excel 2007 under the Developer Tab. Did
they remove it or should I look somewhere else for it?
Thanks



"Bob Phillips" wrote:

> I use the code librarian that came with Excel 2000 Developer, but I always
> have trouble re-installing it when I rebuild, so I am thinking of going with
> KeyNote.
>
> --
>
> HTH
>
> Bob
>
> "Gord Dibben" <gorddibbATshawDOTca> wrote in message
> news:upr4r5hvelahdblbc7gfkqnfpnnqvj7fm4(a)4ax.com...
> > Save what?
> >
> > Just the formula for future use?
> >
> > I keep a workbook handy where I stick all such stuff with an explanation
> > of
> > what it does.
> >
> > Several worksheets.......Indirect, Sumproduct, Vlookup
> >
> > Copy the formula and paste it.
> >
> > Precede it with an apostrophe so's it is visible as text.
> >
> > I keep that workbook always open for testing.
> >
> > I also store macros and code in several modules named appropriately in an
> > add-in which is always loaded for testing code.
> >
> >
> > Gord Dibben MS Excel MVP
> >
> > On Tue, 30 Mar 2010 14:12:02 -0700, MrMike
> > <MrMike(a)discussions.microsoft.com> wrote:
> >
> >>One more thing, is there anyway to save this in excel so I can get to it
> >>quickly without having to refer to any notes?
> >>
> >>
> >>"David Biddulph" wrote:
> >>
> >>> Isn't it as simple as changing
> >>> =TEXT(SUBSTITUTE(A1,"-",""),"(###) ###-####")
> >>> to
> >>> =TEXT(RIGHT(SUBSTITUTE(A1,"-",""),10),"(###) ###-####") ?
> >>>
> >>> Why wouldn't that work?
> >>> --
> >>> David Biddulph
> >>>
> >>>
> >>> "MrMike" <MrMike(a)discussions.microsoft.com> wrote in message
> >>> news:B28C8048-E83D-4615-8CDA-B11F2A2CFD17(a)microsoft.com...
> >>> > This is also a good start, which is more of what I'm looking for,
> >>> > however
> >>> > I
> >>> > now have a 4 digit area code because of the on before the number. Is
> >>> > there a
> >>> > way to do this and remove just the 1 before the before number? All
> >>> > the
> >>> > other
> >>> > functions I've tried removes all the ones in the phone number which is
> >>> > not
> >>> > what I want to do.
> >>> >
> >>> > "Bob Phillips" wrote:
> >>> >
> >>> >> Try
> >>> >>
> >>> >> =TEXT(SUBSTITUTE(A1,"-",""),"(###) ###-####")
> >>> >>
> >>> >> --
> >>> >>
> >>> >> HTH
> >>> >>
> >>> >> Bob
> >>> >>
> >>> >> "MrMike" <MrMike(a)discussions.microsoft.com> wrote in message
> >>> >> news:2B343330-00F7-4DE6-A6F8-9252C12C6630(a)microsoft.com...
> >>> >> >I have phone numbers in two different formats:
> >>> >> > 1. 1-123-456-7890 (11 digits w/dashes)
> >>> >> > 2. 1234567890 (10 digits only)
> >>> >> >
> >>> >> > How can I convert the first format into the standard format in
> >>> >> > excel
> >>> >> > for
> >>> >> > phone numbers of (###) ###-####?
> >>> >> >
> >>> >> > I know the second format will do it automatically.
> >>> >> >
> >>> >> > Thanks in advance for your help.
> >>> >>
> >>> >>
> >>> >> .
> >>> >>
> >>>
> >>> .
> >>>
> >
>
>
> .
>
From: Bob Phillips on 31 Mar 2010 13:01
No you won't, it was an extra that came with the Excel 2000 Developer
edition (which I still have).

--

HTH

Bob

"MrMike" <MrMike(a)discussions.microsoft.com> wrote in message
news:F1B90CDF-8EC0-4ADE-855A-778EFF2D3434(a)microsoft.com...
> Bob,
> I don't see a code librarian in Excel 2007 under the Developer Tab. Did
> they remove it or should I look somewhere else for it?
> Thanks
>
>
>
> "Bob Phillips" wrote:
>
>> I use the code librarian that came with Excel 2000 Developer, but I
>> always
>> have trouble re-installing it when I rebuild, so I am thinking of going
>> with
>> KeyNote.
>>
>> --
>>
>> HTH
>>
>> Bob
>>
>> "Gord Dibben" <gorddibbATshawDOTca> wrote in message
>> news:upr4r5hvelahdblbc7gfkqnfpnnqvj7fm4(a)4ax.com...
>> > Save what?
>> >
>> > Just the formula for future use?
>> >
>> > I keep a workbook handy where I stick all such stuff with an
>> > explanation
>> > of
>> > what it does.
>> >
>> > Several worksheets.......Indirect, Sumproduct, Vlookup
>> >
>> > Copy the formula and paste it.
>> >
>> > Precede it with an apostrophe so's it is visible as text.
>> >
>> > I keep that workbook always open for testing.
>> >
>> > I also store macros and code in several modules named appropriately in
>> > an
>> > add-in which is always loaded for testing code.
>> >
>> >
>> > Gord Dibben MS Excel MVP
>> >
>> > On Tue, 30 Mar 2010 14:12:02 -0700, MrMike
>> > <MrMike(a)discussions.microsoft.com> wrote:
>> >
>> >>One more thing, is there anyway to save this in excel so I can get to
>> >>it
>> >>quickly without having to refer to any notes?
>> >>
>> >>
>> >>"David Biddulph" wrote:
>> >>
>> >>> Isn't it as simple as changing
>> >>> =TEXT(SUBSTITUTE(A1,"-",""),"(###) ###-####")
>> >>> to
>> >>> =TEXT(RIGHT(SUBSTITUTE(A1,"-",""),10),"(###) ###-####") ?
>> >>>
>> >>> Why wouldn't that work?
>> >>> --
>> >>> David Biddulph
>> >>>
>> >>>
>> >>> "MrMike" <MrMike(a)discussions.microsoft.com> wrote in message
>> >>> news:B28C8048-E83D-4615-8CDA-B11F2A2CFD17(a)microsoft.com...
>> >>> > This is also a good start, which is more of what I'm looking for,
>> >>> > however
>> >>> > I
>> >>> > now have a 4 digit area code because of the on before the number.
>> >>> > Is
>> >>> > there a
>> >>> > way to do this and remove just the 1 before the before number? All
>> >>> > the
>> >>> > other
>> >>> > functions I've tried removes all the ones in the phone number which
>> >>> > is
>> >>> > not
>> >>> > what I want to do.
>> >>> >
>> >>> > "Bob Phillips" wrote:
>> >>> >
>> >>> >> Try
>> >>> >>
>> >>> >> =TEXT(SUBSTITUTE(A1,"-",""),"(###) ###-####")
>> >>> >>
>> >>> >> --
>> >>> >>
>> >>> >> HTH
>> >>> >>
>> >>> >> Bob
>> >>> >>
>> >>> >> "MrMike" <MrMike(a)discussions.microsoft.com> wrote in message
>> >>> >> news:2B343330-00F7-4DE6-A6F8-9252C12C6630(a)microsoft.com...
>> >>> >> >I have phone numbers in two different formats:
>> >>> >> > 1. 1-123-456-7890 (11 digits w/dashes)
>> >>> >> > 2. 1234567890 (10 digits only)
>> >>> >> >
>> >>> >> > How can I convert the first format into the standard format in
>> >>> >> > excel
>> >>> >> > for
>> >>> >> > phone numbers of (###) ###-####?
>> >>> >> >
>> >>> >> > I know the second format will do it automatically.
>> >>> >> >
>> >>> >> > Thanks in advance for your help.
>> >>> >>
>> >>> >>
>> >>> >> .
>> >>> >>
>> >>>
>> >>> .
>> >>>
>> >
>>
>>
>> .
>>


From: MrMike on 31 Mar 2010 14:30
Do you know if that extra can be downloaded or purchased and added into Excel
2007?

"Bob Phillips" wrote:

> No you won't, it was an extra that came with the Excel 2000 Developer
> edition (which I still have).
>
> --
>
> HTH
>
> Bob
>
> "MrMike" <MrMike(a)discussions.microsoft.com> wrote in message
> news:F1B90CDF-8EC0-4ADE-855A-778EFF2D3434(a)microsoft.com...
> > Bob,
> > I don't see a code librarian in Excel 2007 under the Developer Tab. Did
> > they remove it or should I look somewhere else for it?
> > Thanks
> >
> >
> >
> > "Bob Phillips" wrote:
> >
> >> I use the code librarian that came with Excel 2000 Developer, but I
> >> always
> >> have trouble re-installing it when I rebuild, so I am thinking of going
> >> with
> >> KeyNote.
> >>
> >> --
> >>
> >> HTH
> >>
> >> Bob
> >>
> >> "Gord Dibben" <gorddibbATshawDOTca> wrote in message
> >> news:upr4r5hvelahdblbc7gfkqnfpnnqvj7fm4(a)4ax.com...
> >> > Save what?
> >> >
> >> > Just the formula for future use?
> >> >
> >> > I keep a workbook handy where I stick all such stuff with an
> >> > explanation
> >> > of
> >> > what it does.
> >> >
> >> > Several worksheets.......Indirect, Sumproduct, Vlookup
> >> >
> >> > Copy the formula and paste it.
> >> >
> >> > Precede it with an apostrophe so's it is visible as text.
> >> >
> >> > I keep that workbook always open for testing.
> >> >
> >> > I also store macros and code in several modules named appropriately in
> >> > an
> >> > add-in which is always loaded for testing code.
> >> >
> >> >
> >> > Gord Dibben MS Excel MVP
> >> >
> >> > On Tue, 30 Mar 2010 14:12:02 -0700, MrMike
> >> > <MrMike(a)discussions.microsoft.com> wrote:
> >> >
> >> >>One more thing, is there anyway to save this in excel so I can get to
> >> >>it
> >> >>quickly without having to refer to any notes?
> >> >>
> >> >>
> >> >>"David Biddulph" wrote:
> >> >>
> >> >>> Isn't it as simple as changing
> >> >>> =TEXT(SUBSTITUTE(A1,"-",""),"(###) ###-####")
> >> >>> to
> >> >>> =TEXT(RIGHT(SUBSTITUTE(A1,"-",""),10),"(###) ###-####") ?
> >> >>>
> >> >>> Why wouldn't that work?
> >> >>> --
> >> >>> David Biddulph
> >> >>>
> >> >>>
> >> >>> "MrMike" <MrMike(a)discussions.microsoft.com> wrote in message
> >> >>> news:B28C8048-E83D-4615-8CDA-B11F2A2CFD17(a)microsoft.com...
> >> >>> > This is also a good start, which is more of what I'm looking for,
> >> >>> > however
> >> >>> > I
> >> >>> > now have a 4 digit area code because of the on before the number.
> >> >>> > Is
> >> >>> > there a
> >> >>> > way to do this and remove just the 1 before the before number? All
> >> >>> > the
> >> >>> > other
> >> >>> > functions I've tried removes all the ones in the phone number which
> >> >>> > is
> >> >>> > not
> >> >>> > what I want to do.
> >> >>> >
> >> >>> > "Bob Phillips" wrote:
> >> >>> >
> >> >>> >> Try
> >> >>> >>
> >> >>> >> =TEXT(SUBSTITUTE(A1,"-",""),"(###) ###-####")
> >> >>> >>
> >> >>> >> --
> >> >>> >>
> >> >>> >> HTH
> >> >>> >>
> >> >>> >> Bob
> >> >>> >>
> >> >>> >> "MrMike" <MrMike(a)discussions.microsoft.com> wrote in message
> >> >>> >> news:2B343330-00F7-4DE6-A6F8-9252C12C6630(a)microsoft.com...
> >> >>> >> >I have phone numbers in two different formats:
> >> >>> >> > 1. 1-123-456-7890 (11 digits w/dashes)
> >> >>> >> > 2. 1234567890 (10 digits only)
> >> >>> >> >
> >> >>> >> > How can I convert the first format into the standard format in
> >> >>> >> > excel
> >> >>> >> > for
> >> >>> >> > phone numbers of (###) ###-####?
> >> >>> >> >
> >> >>> >> > I know the second format will do it automatically.
> >> >>> >> >
> >> >>> >> > Thanks in advance for your help.
> >> >>> >>
> >> >>> >>
> >> >>> >> .
> >> >>> >>
> >> >>>
> >> >>> .
> >> >>>
> >> >
> >>
> >>
> >> .
> >>
>
>
> .
>
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Countif function to show how often numbers within a range are pres
Next: GETPIVOTDATE Funtion