How change "535?4090" to "535-4090" using replace

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Allewyn - 20 Dec 2005 22:42 GMT

Column of 1000 phone numbers. I've managed to eliminate area code using
replace with wildcards but can't seem to change the ? to a dash. Everything
I try completely messes up the sheet and I have to undo. Even had to restore
once! Gah! Any help out there? :)

Reply to this Message

Dave Peterson - 20 Dec 2005 22:47 GMT

Try ~?
(tilde question mark)

That tilde tells excel that you don't want the wild card for a single character
(?), you really want the question mark itself.

Same if you have to change an asterisk (* becomes ~*) or the tilde (~ becomes
~~).

> Column of 1000 phone numbers. I've managed to eliminate area code using
> replace with wildcards but can't seem to change the ? to a dash. Everything
> I try completely messes up the sheet and I have to undo. Even had to restore
> once! Gah! Any help out there? :)

Signature

Dave Peterson

Reply to this Message

Allewyn - 20 Dec 2005 22:53 GMT

Soory, do you mean I should type 535~4090 and replace with 535-4090?

> Try ~?
> (tilde question mark)
[quoted text clipped - 9 lines]
> > I try completely messes up the sheet and I have to undo. Even had to restore
> > once! Gah! Any help out there? :)

Reply to this Message

Allewyn - 20 Dec 2005 23:06 GMT

I probably wasn't saying what I meant
Ok, let me try explaining this again to be complete:
772?8155
773?4006
772?1927
773?8250
770?9553
858?1911
245?3080
772?8527
776?2755
608?3823
This column has "?"s and I want "-"s instead. There are different prefixes
so that's why I tried wild cards to get where I am now.
I understand what you mean by using tilde but here's what happens
Replacing *~?* with *-* gives *-* in the cells
Replacing *~?* with *- gives *- in the cells
Replacing *?* with *-* wipes out the sheet (dashes everywhere)
Replacing ? with - crashed Excel .
Should I be replacing each prefix separately or is there a way to do them
all at one time?

> Try ~?
> (tilde question mark)
[quoted text clipped - 9 lines]
> > I try completely messes up the sheet and I have to undo. Even had to restore
> > once! Gah! Any help out there? :)

Reply to this Message

Trevor Shuttleworth - 20 Dec 2005 22:51 GMT

Try replacing "~?" with "-" ... without the quotes

The question mark is a wild card in its own right so you need to tell Excel
to actually look for a question mark ... and that's what the tilda does.

So, Replace all ~? with -

Regards

Trevor

> Column of 1000 phone numbers. I've managed to eliminate area code using
> replace with wildcards but can't seem to change the ? to a dash.
> Everything
> I try completely messes up the sheet and I have to undo. Even had to
> restore
> once! Gah! Any help out there? :)

Reply to this Message

Allewyn - 20 Dec 2005 23:10 GMT

Yay! That did it. Thanks!

> Try replacing "~?" with "-" ... without the quotes
>
[quoted text clipped - 13 lines]
> > restore
> > once! Gah! Any help out there? :)

Reply to this Message