Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Preserving Text Only despite Excel's # Recognition Processes
For Microsoft: Why not exempt the Text Only cell format from parsing or date
corrections? (First, I'm VBA ignorant.) I've been working on converting text strings like "SUPPX2", "PHNX3", and "SUPP/PHNX2" into counts of services; per my examples: 2, 3, 3. Each time I've used #s as replacements, I keep getting dates in cells that were formatted as Text only. As described in other posts, "1/1" becomes "1-Jan" and "2/3" becomes "2-Mar". When I reformat the cells to Text Only, the new dates become meaningless numbers (I'm guessing Julian numbers for the specified dates, but I don't read Julian and don't like having to guess which # corresponds to the "1/1" that I wanted.) This results in my having to undo all the work I've done, just to get back to the original. As a result, I've been forced to locate textual replacements that wouldn't be mistaken as numbers or Excel-recognized symbols. Example: Replacing "SUPP" with "-" makes "SUPPX3" into "-X3" which Excel sees as a reference to cell X3. (Don't know why, grrr.) Changing "PHN/SUPPX2" by the same method results in cell errors referring to invalid formulas. (double grrr.) I finally found that "+" either singly, or in conjunction with other "+"s or symbols (letters), does not prompt Excel to either reformat the cell or register an error, but this leaves me with the following process: I use the following replacements (= is used in place of "is replaced with" & "" is a blank): "INTAKE"=""; "PHONE"="+"; "SUPP"="+"; "+X2"="++"; "+X3"="+++"; "+X4"="++++"; "+X5"="+++++"; "+/+"="++"; "+++++++++"="9"; "++++++++"="8"; "+++++++"="7"; "++++++"="6"; "+++++"="5"; "++++"="4"; "+++"="3"; "++"="2"; "+"="1". Is there a way to force Text Only to be maintained despite the presence of a number pattern or number in the cell? (I've read other posts on this issue, but I'm wondering if anything new has been done to rectify this problem.) -- Mike S. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...lic.excel.misc |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Preserving Text Only despite Excel's # Recognition Processes
When I need to have Excel ignore a number in a text field, I precede the
number with the ' character. Forces Excel to ignore the numbers and treat them as text, and doesn't display in the actual field with the ', just in the formula bar. HTH, at least it's worth trying. Wood *** "Mike S." wrote: For Microsoft: Why not exempt the Text Only cell format from parsing or date corrections? (First, I'm VBA ignorant.) I've been working on converting text strings like "SUPPX2", "PHNX3", and "SUPP/PHNX2" into counts of services; per my examples: 2, 3, 3. Each time I've used #s as replacements, I keep getting dates in cells that were formatted as Text only. As described in other posts, "1/1" becomes "1-Jan" and "2/3" becomes "2-Mar". When I reformat the cells to Text Only, the new dates become meaningless numbers (I'm guessing Julian numbers for the specified dates, but I don't read Julian and don't like having to guess which # corresponds to the "1/1" that I wanted.) This results in my having to undo all the work I've done, just to get back to the original. As a result, I've been forced to locate textual replacements that wouldn't be mistaken as numbers or Excel-recognized symbols. Example: Replacing "SUPP" with "-" makes "SUPPX3" into "-X3" which Excel sees as a reference to cell X3. (Don't know why, grrr.) Changing "PHN/SUPPX2" by the same method results in cell errors referring to invalid formulas. (double grrr.) I finally found that "+" either singly, or in conjunction with other "+"s or symbols (letters), does not prompt Excel to either reformat the cell or register an error, but this leaves me with the following process: I use the following replacements (= is used in place of "is replaced with" & "" is a blank): "INTAKE"=""; "PHONE"="+"; "SUPP"="+"; "+X2"="++"; "+X3"="+++"; "+X4"="++++"; "+X5"="+++++"; "+/+"="++"; "+++++++++"="9"; "++++++++"="8"; "+++++++"="7"; "++++++"="6"; "+++++"="5"; "++++"="4"; "+++"="3"; "++"="2"; "+"="1". Is there a way to force Text Only to be maintained despite the presence of a number pattern or number in the cell? (I've read other posts on this issue, but I'm wondering if anything new has been done to rectify this problem.) -- Mike S. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...lic.excel.misc |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Preserving Text Only despite Excel's # Recognition Processes
So, does this mean that I need to replace "SUPP" with '1, repeating the
process with "PHN" and the "X2"s, "X3"s, and "X4"s? Or am I getting the process confused? (So SUPP/PHN becomes '1/'1, and SUPPx4/PHNX3 becomes '4/'3...) Thanks for your assistance. -- Mike S. "Wood Grafing" wrote: When I need to have Excel ignore a number in a text field, I precede the number with the ' character. Forces Excel to ignore the numbers and treat them as text, and doesn't display in the actual field with the ', just in the formula bar. HTH, at least it's worth trying. Wood *** "Mike S." wrote: For Microsoft: Why not exempt the Text Only cell format from parsing or date corrections? (First, I'm VBA ignorant.) I've been working on converting text strings like "SUPPX2", "PHNX3", and "SUPP/PHNX2" into counts of services; per my examples: 2, 3, 3. Each time I've used #s as replacements, I keep getting dates in cells that were formatted as Text only. As described in other posts, "1/1" becomes "1-Jan" and "2/3" becomes "2-Mar". When I reformat the cells to Text Only, the new dates become meaningless numbers (I'm guessing Julian numbers for the specified dates, but I don't read Julian and don't like having to guess which # corresponds to the "1/1" that I wanted.) This results in my having to undo all the work I've done, just to get back to the original. As a result, I've been forced to locate textual replacements that wouldn't be mistaken as numbers or Excel-recognized symbols. Example: Replacing "SUPP" with "-" makes "SUPPX3" into "-X3" which Excel sees as a reference to cell X3. (Don't know why, grrr.) Changing "PHN/SUPPX2" by the same method results in cell errors referring to invalid formulas. (double grrr.) I finally found that "+" either singly, or in conjunction with other "+"s or symbols (letters), does not prompt Excel to either reformat the cell or register an error, but this leaves me with the following process: I use the following replacements (= is used in place of "is replaced with" & "" is a blank): "INTAKE"=""; "PHONE"="+"; "SUPP"="+"; "+X2"="++"; "+X3"="+++"; "+X4"="++++"; "+X5"="+++++"; "+/+"="++"; "+++++++++"="9"; "++++++++"="8"; "+++++++"="7"; "++++++"="6"; "+++++"="5"; "++++"="4"; "+++"="3"; "++"="2"; "+"="1". Is there a way to force Text Only to be maintained despite the presence of a number pattern or number in the cell? (I've read other posts on this issue, but I'm wondering if anything new has been done to rectify this problem.) -- Mike S. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...lic.excel.misc |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Preserving Text Only despite Excel's # Recognition Processes
You only need the ' at the beginning of the cell, not for each entry. The '
forces excel to treat everything after it as text. "Mike S." wrote: So, does this mean that I need to replace "SUPP" with '1, repeating the process with "PHN" and the "X2"s, "X3"s, and "X4"s? Or am I getting the process confused? (So SUPP/PHN becomes '1/'1, and SUPPx4/PHNX3 becomes '4/'3...) Thanks for your assistance. -- Mike S. "Wood Grafing" wrote: When I need to have Excel ignore a number in a text field, I precede the number with the ' character. Forces Excel to ignore the numbers and treat them as text, and doesn't display in the actual field with the ', just in the formula bar. HTH, at least it's worth trying. Wood *** "Mike S." wrote: For Microsoft: Why not exempt the Text Only cell format from parsing or date corrections? (First, I'm VBA ignorant.) I've been working on converting text strings like "SUPPX2", "PHNX3", and "SUPP/PHNX2" into counts of services; per my examples: 2, 3, 3. Each time I've used #s as replacements, I keep getting dates in cells that were formatted as Text only. As described in other posts, "1/1" becomes "1-Jan" and "2/3" becomes "2-Mar". When I reformat the cells to Text Only, the new dates become meaningless numbers (I'm guessing Julian numbers for the specified dates, but I don't read Julian and don't like having to guess which # corresponds to the "1/1" that I wanted.) This results in my having to undo all the work I've done, just to get back to the original. As a result, I've been forced to locate textual replacements that wouldn't be mistaken as numbers or Excel-recognized symbols. Example: Replacing "SUPP" with "-" makes "SUPPX3" into "-X3" which Excel sees as a reference to cell X3. (Don't know why, grrr.) Changing "PHN/SUPPX2" by the same method results in cell errors referring to invalid formulas. (double grrr.) I finally found that "+" either singly, or in conjunction with other "+"s or symbols (letters), does not prompt Excel to either reformat the cell or register an error, but this leaves me with the following process: I use the following replacements (= is used in place of "is replaced with" & "" is a blank): "INTAKE"=""; "PHONE"="+"; "SUPP"="+"; "+X2"="++"; "+X3"="+++"; "+X4"="++++"; "+X5"="+++++"; "+/+"="++"; "+++++++++"="9"; "++++++++"="8"; "+++++++"="7"; "++++++"="6"; "+++++"="5"; "++++"="4"; "+++"="3"; "++"="2"; "+"="1". Is there a way to force Text Only to be maintained despite the presence of a number pattern or number in the cell? (I've read other posts on this issue, but I'm wondering if anything new has been done to rectify this problem.) -- Mike S. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/comm...lic.excel.misc |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Preserving Text Only despite Excel's # Recognition Processes
Thanks,
Your advice has helped. -- Mike S. "Wood Grafing" wrote: You only need the ' at the beginning of the cell, not for each entry. The ' forces excel to treat everything after it as text. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Preserving Text Only despite Excel's # Recognition Processes
Another approach, rather than using Find & Replace, is to use a
formula which makes use of several SUBSTITUTE functions - this will return text. The formula can be copied down the column as required, and then the values can be fixed in place. Hope this helps. Pete On Jul 13, 9:08 pm, Mike S. wrote: Thanks, Your advice has helped. -- Mike S. "Wood Grafing" wrote: You only need the ' at the beginning of the cell, not for each entry. The ' forces excel to treat everything after it as text.- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Preserving Cell References | Excel Discussion (Misc queries) | |||
Text recognition | Excel Discussion (Misc queries) | |||
Preserving customization | Setting up and Configuration of Excel | |||
preserving cell format | Excel Discussion (Misc queries) | |||
Preserving Formulas | Excel Discussion (Misc queries) |