A few years ago there was a solar eclipse. Those are rare events, so when the occur people want to see it. However, it's one of those things we're told never to look at directly. We're told to use pinhole viewers or some other contraption to view solar eclipses. So how are some special characters like a solar eclipse when we need to use them in a spreasheet? Some special characters need to be used indirectly.
Cover Image made using HTML and CSS. Light edits made using MS Paint. Additional images made using Excel 2003, with edits made using MS Paint.
Context Is Enough for Most Spcial Characters
Whether you use Excel or some other spreadsheet program, there are some characters which mean one thing normally but mean something else in a different context. Usually context alone is enough to let the program figure out how to handle it:
=A1+B1// "=" starts a formula=IF(C2="","N/A",C2)// "=" starts a formula and is used for comparison="Bitcoin = " & C2// "=" starts a formula and is used as text
For special characters such as=, it's rare to have to do anything to escape them for use as text. Anything between open"and close"is treated as text.
But what happens if the " itself needs to be treated as text? The spreadsheet needs a way to know that, otherwise the formula returns an error. This is where escaping characters such as " is needed.
Escaping Special Characters
In mose spreadsheet programs, escaping characters is done using the backslash-- \-- before the special character. In the case of that pesky " character, here is an example:
="Today is " & TODAY() & " and I published the post \"Isn't That Special?\""
For clarity, here is the example reformated:
="Today is " &
TODAY() &
" and I published the post \"Isn't That Special?\""
" begins and ends a text string. \" turns the special character into just another text character to be displayed or processed. as long as we keep this in mind, this should be as far as we need top go.
However, visually, we see this--
"//"in its native form as a special character\"// An escaped"which turns it into text\""// An escaped close"for text before the real closed"the spreadsheet needs
-- which makes it easy to type this sooner or later:
""\"// A real open"the spreadsheet needs before the escaped open"for text
Keeping track of backslashes and quotation marks can be challenging, to say the least.
Luckily for us, there are other ways to clarify the formula, even if the formula becomes a bit longer.
Other Ways To Display Troublesome Special Characters
Enter " in a Cell by Itself, Then Refer to That Cell
Let cell A1 contain ". Instead of using the formula
="Today is " & TODAY() & " and I published the post \"Isn't That Special?\""
Use this formula instead:
="Today is " & TODAY() & " and I published the post" & $A$1 & "Isn't That Special?" & $A$1
Here the " that's visible is used as intended, and the text quote appears in the form of an absolute reference to cell A1.
Use the CHAR() Function
Every character we use has an ASCII code associated with it. Tables of ASCII codes can be found, and Excel can even be used to generate an ASCII table. Here, we need to know that the ASCII code for " is 34.
Using that detail, the text formula can be rewritten this way:
="Today is " & TODAY() & " and I published the post" & CHAR(34) & "Isn't That Special?" & CHAR(34)
Define a Named Range Containing Either " or CHAR(34)
Back to Cell A1-- Place either " or CHAR(34) in A1; either will produce " on screen. Then define a named range using Cell A1. There are two ways to define a named range, but the following image and explanation about the Name Box after it shows the easier method that's more commonly used:
Click the image for a full-size view
| About the Name Box |
|---|
| Everyone who uses a spreadsheet program is familiar with the Formular Bar, usually near the top of the worksheet area over the column letters. Less known, but just as visible and important, is the Name Box locate to the left of the Formula bar. Normally it contains the address of the active cell. However, itis also used to select from a list of named ranges. It can also be used to create a named range (even a 1x1 range, which is just a cell). Click inside the Name Box to highlight the cell address, then enter the range (either something like D4:F18 or K9) to be named, give it a relevant name, then hit ENTER. Now the named range appears in the drop-down list of the NameBox, and whenever the range is selected the name of the named range appears in the Name Box. |
(There's another way to define a named range-- even without cell addresses-- but that methd will be explained in a future post.)
If a Character Is That Pesky, Why Not Use Another One?
Why not, indeed. A number of sutable alternatives for double-quotes character " exist, but they are not easily accessible. First they need to be located, then they need to be placed as needed.
What characters can be possible replacements?
- The pair
«(ASCII Code 171) and»(ASCII Code 187).
These characters are actual quote marks in certain regional settings of many programs. They can be copied and pasted into any piece of text. Just be careful when copying from the spreadsheet and pasted into a program such as a text editor made for programming; that program will insist on the actual"character.
Just My Two Sats
It's surprising how much trouble one character can cause! Spreadsheet programs use certain characters for for their own purposes, so when they need to be used as text it can be a bit awkward. In this regard, special characters are like solar eclipses: both need indirect means to be seen or used.
Usually context lets the spreadshet know that the character is being used as text. However, some characters are more troublesome than others. Among these pesky characters is the double-quote, ".
Pesky or not, before a special character can be used it needs to be escaped. In Excel and its imitators, the backslash, \ is the escape character which preceds the pesky character.
If a text-based formula contains many " characters, it can be a challenge making sure the correct " is preceded by \. One workaround is to break up the text formula at each " and replace it with a cell reference, CHAR(34), or a one-cell named range containing either " or CHAR(34).
It's possible for alternate characters to be used in place of " if the spreadsheet accepts them, including the double-prime character (Decimal code 8243), but these characters aren't easy to locate. Just be careful if the text containing these alternate characters is copied and pasted into a program which doesn't handle them too well.
| ● If you liked this post, please give it an upvote. ● If you liked this post but it's past the 7-day payout window, please tip using PIZZA, BEER, or the engagement token of your choice (or just send something from your account to mine). ● Please reblog or re-Hive this post if you found it useful. ● If there are corrections or clarifications I need to make concerning post content, let me know in a comment so I can update the post and give you credit for the correction. ● As I am neither a certified professional accountant nor a licensed broker, posts concerning cryptocurrency, commodities, securities, and money are presented for informational purposes only; DYOR. |
|---|
| ● If you're new to cryptocurrency and want to know what people think, feel free to drop by the Daily Crypto Markets Live Blog at LeoFinance. It's a free-wheeling text conversation comment thread that takes place in (more or less) real-time. ● Although I've learned a lot about Hive and its ecosystem of dApps, there's much more I don't know. Where can Hivers new and old learn more? Visit TheTerminal for FAQs, tips, references, and terrific how-to content. ● Did you know that there are 156 dApps built by Hive community members, and more to come? Discover them at Hive Projects. ● If your voting power is beneath dusting threshold, follow and use @dustsweeper and @dustbunny to boost your voting power. Authors you upvote will be glad you did, and you'll be happy to start claiming curation rewards. |
| Sometimes I discover posts way after the initial earnings period. It's too late for me to upvote posts then, so to make up for the lost upvote I send a slice of PIZZA instead. My main focus is cryptocurrencies from a number of angles (educational, commentary, observations, even pop culture). A secondary focus is sharing my discoveries about the world of Hive Social. Most of my posts can be found at these two locations: ● LeoFinance : @magnacarta ● Proof of Brain : @magnacarta Follow me at this D.Buzz-only account : ● D.Buzz : https://blog.d.buzz/#/@magnacarta.buzz-- note the ".buzz" at the end! If you follow me at @magnacarta.buzz, please follow me at this account as well I expect 2022 to be the year I go from being crazy or weird to being eccentric. If that doesn't happen, then Festivus will last longer than I can stand. |