And by having the format of the field consistent, I can use =vlookup() and
=match() without having to worry about if the data is text or numeric.
Dave Peterson wrote:
>
> I think that there was an unfortunate line break in the middle of his examples:
>
> I have an excel file with a column for phone numbers. Some are (xxx)
> xxx-xxxx and others are xxx-xxx-xxxx.
>
> I think the first example was really:
> (xxx) xxx-xxxx
> So there would have been an extra space in that string.
>
> And as a personal choice, I'd want all my data the same format--either all text
> or all numbers (nicely formatted).
>
> I'd convert any text strings xxx-xxx-xxxx to its number and then use
> format|cells to give the whole range a nice consistent look.
>
> Gord Dibben wrote:
> >
> > I'm not sure why OP needs any more than two edit>replaces to get rid of the ()
> >
> > Some data looks like (xxx)xxx-xxxx
> >
> > Wants it to look like xxx-xxx-xxxx
> >
> > Edit>Replace
> >
> > what: (
> > with: nothing
> >
> > Edit>Replace
> >
> > what: )
> > with: - My first post was replace with nothing which was incorrect.
> >
> > returns xxx-xxx-xxxx
> >
> > I guess I don't understand why the need to trun the phone number into a number
> > just to turn around and custom format as
> >
> > xxx-xxx-xxxx
> >
> > Gord
> >
> > On Wed, 23 Aug 2006 14:12:51 -0500, Dave Peterson <(E-Mail Removed)>
> > wrote:
> >
> > >Add one more edit|replace
> > >
> > >Edit|Replace
> > >what: (spacebar)
> > >with: (leave blank)
> > >replace all
> > >
> > >CBickley wrote:
> > >>
> > >> Thanks, this worked. Now I have a space in the middle of the numbers that I
> > >> need to delete (xxx xxxxxxx). How do I do this?
> > >>
> > >> "Dave Peterson" wrote:
> > >>
> > >> > If those () and -'s are really part of the cell (not formatting choices),
> > >> >
> > >> > Then select the column
> > >> > and do 3 edit|replaces
> > >> >
> > >> > edit|replace
> > >> > what: (
> > >> > with: (leave blank)
> > >> > replace all
> > >> >
> > >> > edit|replace
> > >> > what: )
> > >> > with: (leave blank)
> > >> > replace all
> > >> >
> > >> > edit|replace
> > >> > what: -
> > >> > with: (leave blank)
> > >> > replace all
> > >> >
> > >> > Now all your entries should be real numbers and your format|cells|number tab
> > >> > choices should work ok.
> > >> >
> > >> >
> > >> > CBickley wrote:
> > >> > >
> > >> > > I have an excel file with a column for phone numbers. Some are (xxx)
> > >> > > xxx-xxxx and others are xxx-xxx-xxxx. I need to make all the numbers follow
> > >> > > the xxx-xxx-xxxx format but am unable to do so with the format cells. What
> > >> > > do I need to do?
> > >> >
> > >> > --
> > >> >
> > >> > Dave Peterson
> > >> >
> >
> > Gord Dibben MS Excel MVP
>
> --
>
> Dave Peterson
--
Dave Peterson
|
Similar Threads
