Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How change "535?4090" to "535-4090" using replace
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? :) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How change "535?4090" to "535-4090" using replace
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 ~~). Allewyn wrote: 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? :) -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How change "535?4090" to "535-4090" using replace
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 "Allewyn" wrote in message ... 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? :) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How change "535?4090" to "535-4090" using replace
Soory, do you mean I should type 535~4090 and replace with 535-4090?
"Dave Peterson" wrote: 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 ~~). Allewyn wrote: 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? :) -- Dave Peterson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How change "535?4090" to "535-4090" using replace
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? "Dave Peterson" wrote: 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 ~~). Allewyn wrote: 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? :) -- Dave Peterson |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How change "535?4090" to "535-4090" using replace
Yay! That did it. Thanks!
"Trevor Shuttleworth" wrote: 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 "Allewyn" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find function | Excel Worksheet Functions | |||
Find and replace of word causes change of font formatting | New Users to Excel | |||
Cell Change Color - Need Help | New Users to Excel | |||
use replace command to change the contents of formulas | Excel Discussion (Misc queries) | |||
REPLACE outside of highlighted column | Excel Worksheet Functions |