Markdown is a text based language basically designed to allow you to do simple code while working in a text editor. The curious can learn more here: daringfireball.net/projects/markdown/. I bring this up because as it is approaching the end of the year, I have been tidying up my annual book list (2013, 2012) to get ready to post in the new year. In the past I have prepared the formatting of this list a number of ways from using search and replace to typing it directly into WordPress. In the end, I have found that maintaining a text file using IA Writer which syncs between all my mobile devices and that has the prepared html code has been easiest. I use this:

<i><strong>title</strong></i> author (date)<br>
<b>series</b> Book # – ebook; reread

And it shows this:
title author (date)
series Book # – ebook; reread

I simply copy and paste the code, fill in the proper info and am good to go. The only downside is occasionally I forget to copy the code and then have to recreate the code template. But that’s not too much effort so I haven’t worried about it. But a little reading about Markdown is convincing me that next year I should be able to simplify the procedure. The theory is if I type:

_**Cally's War**_ John Ringo & Julie Cochrane (2004)
**Cally's War** Book 1 - ebook; reread

It should come out as:

Cally’s War John Ringo & Julie Cochrane (2004)
Cally’s War Book 1 – ebook; reread

And look at that, it totally does… You can agree that it’s a whole hell of a lot less typing to format the entries using Markdown than traditional html. I just have to learn a little new syntax.

To enable Markdown for use on my WordPress blog I had to activate the default JetPack plugin and sign in to WordPress.com. After that simply activating the Markdown portion converts the code on the fly without destroying the original text-based code. That should make it easier to write all sorts of entries in my text editors.

Markdown Syntax

Here are a few more  examples of Markdown syntax:

This is an H1
=============

This is an H1

===

This is an H2
-------------

This is an H2

or

# This is an H1
## This is an H2
#### This is an H4

etc.

This is an H1

This is an H2

This is an H4

Pretty simple stuff. The rest of this post is an abridged cut-and-paste from the Markdown Syntax page.

Markdown uses > characters for blockquoting. If you’re
familiar with quoting passages of text in an email message, then you
know how to create a blockquote in Markdown. It looks best if you hard
wrap the text and put a > before every line:

> This is a blockquote with two paragraphs. Lorem ipsum dolor sit amet,
> consectetuer adipiscing elit. Aliquam hendrerit mi posuere lectus.
> Vestibulum enim wisi, viverra nec, fringilla in, laoreet vitae, risus.
>
> Donec sit amet nisl. Aliquam semper ipsum sit amet velit. Suspendisse
> id sem consectetuer libero luctus adipiscing.

This is a blockquote with two paragraphs. Lorem ipsum dolor sit amet,
consectetuer adipiscing elit. Aliquam hendrerit mi posuere lectus.
Vestibulum enim wisi, viverra nec, fringilla in, laoreet vitae, risus.

Donec sit amet nisl. Aliquam semper ipsum sit amet velit. Suspendisse
id sem consectetuer libero luctus adipiscing.

Markdown allows you to be lazy and only put the > before the first
line of a hard-wrapped paragraph:

This is a blockquote with two paragraphs. Lorem ipsum dolor sit amet,
consectetuer adipiscing elit. Aliquam hendrerit mi posuere lectus.
Vestibulum enim wisi, viverra nec, fringilla in, laoreet vitae, risus.

Donec sit amet nisl. Aliquam semper ipsum sit amet velit. Suspendisse
id sem consectetuer libero luctus adipiscing.

Blockquotes can be nested (i.e. a blockquote-in-a-blockquote) by
adding additional levels of >:

> This is the first level of quoting.
>
> > This is nested blockquote.
>
> Back to the first level.

This is the first level of quoting.

This is nested blockquote.

Back to the first level.

Markdown supports ordered (numbered) and unordered (bulleted) lists.

Unordered lists use asterisks, pluses, and hyphens — interchangably
— as list markers:

* Red
* Green
* Blue

or

+ Red
+ Green
+ Blue

is

• Red
• Blue
• Green

Ordered lists use numbers followed by periods:

1. Bird
2. McHale
3. Parish

1. Me
2. You
3. Bobby McGee

The one other thing of note is the use of escape characters. A ` before and after a line allows the code to be displayed without applying the formatting or an indent of 4 spaces will show the code in an offset block:

* Bird
* McHale
* Parish

One has to do that in Text view and then post or else WordPress will helpfully delete the blank spaces, so its easiest to use the `

Making Headings

Quite often I would have columns of headings but when I went to export the headings, I would only want the first instance to appear in the text I was using. For example if I was doing a list of recipes and they were divided up into Chicken, Beef and Seafood, I would like the heading “Chicken” to appear only before the first entry—think Table of Contents.

Thus:

Chicken	Recipe 1
Chicken	Recipe 2
Chicken	Recipe 3
Beef	Recipe 1
Beef	Recipe 2
Seafood	Recipe 1
Seafood	Recipe 2
Seafood	Recipe 3

 

Should be:

Chicken	Recipe 1
	Recipe 2
	Recipe 3
Beef	Recipe 1
	Recipe 2
Seafood	Recipe 1
	Recipe 2
	Recipe 3

Again the first thing you ned to do is visualize the problem and then express it logically. If the heading is the same as the heading above then don’t show it. If it is not the same then do show it. I also threw in a bit to accommodate the space between Beef and Seafood in case it was necessary.

The first thing you need to do is insert a new column between the headings (column A) and the recipes (column B). Actually you can make the new column for the calculation anywhere you want but it is much easier to keep alongside the original.

A	B	C
Chicken		Recipe 1
Chicken		Recipe 2
Chicken		Recipe 3
Beef		Recipe 1
Beef		Recipe 2
Seafood		Recipe 1
Seafood		Recipe 2
Seafood		Recipe 3

Then you fill column B with the following formula:

=IF(A2=””,””,(IF(A2=A1,””,A2)))

This is constructed from basic if statements. You will use them a lot if you spend any amount of time working with formulas and calculations. The syntax for an if statement is  IF(logical test, is true then do this…, else do this…) So this =IF(a=b,”A”,”B”) simply put, is  read as if a is equal to b then write A, if not then write B. My formula above uses two nested if statements, one that checks to see if the current row is blank or not and one that determines whether it is the same as the row above.

Is the row empty? If so then leave it blank; if not then go on to the rest of the calculation. Empty (blank) is indicated by two quotes with nothing (not even a space) between them.

=IF(A2=””, “”, next if statement)

The next if statement then decided if the current cell matches the one above or not. If it does, if leaves it blank. If it doesn’t then it put in the text from the current cell.

=IF(A2=A1,””,A2)

All together:

=IF(A2=””,””,(IF(A2=A1,””,A2)))

If cell A2 is blank then leave blank space, if not then see if A2 is equal to the cell above (A1). If it is, then again leave a blank space. If not, then write in the contents of A2.

One thing to remember about this sort of thing is the calculations are all related to the original list. If you delete column A—which you will likely have to if you are going to export it to a text file—then the text in your column B will also disappear. The easiest away around this is, just before you delete the column, you copy column C and then paste it right back using Edit: Paste Special, then select Text. This will paste not the column of formulae you cut, but the results in text form—perfect for exporting!

But I need to get rid of Headers

Just as often I would find that to manipulate the text, it would be easier to not have these headers, but rather the filled in columns (think sorting by header) so how do you do this the other way? Well, there’s a formula for that!

Turning:

apple	Recipe 1
	Recipe 2
	Recipe 3
	Recipe 4
oranges	Recipe 1
	Recipe 2
	Recipe 3
bananas	Recipe 1
	Recipe 2
	Recipe 3
	Recipe 4
	Recipe 5

Into:

apple	Recipe 1
apple	Recipe 2
apple	Recipe 3
apple	Recipe 4
oranges	Recipe 1
oranges	Recipe 2
oranges	Recipe 3
bananas	Recipe 1
bananas	Recipe 2
bananas	Recipe 3
bananas	Recipe 4
bananas	Recipe 5

You begin by once again adding in a new column for the calculation and then filling it with the following:

=(IF(A2=””,B1,A2))

	A	B	C
1
2	apple	=(IF(A2=””,B1,A2)	Recipe 1
3		=(IF(A3=””,B2,A3)	Recipe 2
4		=(IF(A4=””,B3,A4)	Recipe 3
5		=IF(A5=””,”B4,A4)	Recipe 4

In order to make this work you need to have a blank row above your first row so  the calc can always reference the row above. So this calculation says if the row to the left is empty, then make this cell the same as the one above it. If it isn’t empty then make this cell the same as the one to the left. Again remember that to do any sorting or manipulation you first need to change column C from a set of calculations to actual text data (by Paste Special) otherwise changing the order will just make it recalculate based on the new data in the column to the left.

Since I originally started this blog to help me keep track of coding and computer-related info, I thought I would write down some of my favourite Excel formulas for posterity. Most of these reside in an excel spreadsheet I have carried with me for over a dozen years but here they are appearing on the internet for the the very first time!

Last Name First Name

Simple and prevalent but a lot of people just don’t understand how handy spreadsheets can be for manipulating textual data and do it the manual way.

Bill to Name	First	Last
Blow, Joe	Joe	Blow
Simmons, Mike	Mike	Simmons

 

Bill to Name	First	Last
Blow, Joe	=RIGHT(A2,(LEN(A2)-SEARCH(“,”,A2,1))-1)	=LEFT(A2,SEARCH(“,”,A2,1)-1)
Simmons, Mike	=RIGHT(A3,(LEN(A3)-SEARCH(“,”,A3,1))-1)	=LEFT(A3,SEARCH(“,”,A3,1)-1)

A few things to know about calculations

• they all start with the “=” sign.
• calculations are often made up of many, nested, formulae.
• you refer to specific cells by their coordinates in the grid. “A2” refers to the cell in column A, row 2
• spreadsheet calculations are hierarchical, just like you learned in grade school. So brackets and nesting brackets are important. The spreadsheet will solve for the deepest level first and then work outwards.
• Things in quotes are treated as text; things not in quotes are treated as elements of the formula.
• Data elements within a formula are separated by commas

So, in the above, you first need to visualize the problem. Essentially I want everything after the comma to go in column 2 and everything before the comma to go in column 2. So that means first finding the position of the comma.

=SEARCH(“,”,A2,1)
search (for a comma “,”, in cell A2, and look for the first one to appear)

The syntax for this formula and all the others that Excel uses can be found by hitting the fx button right by the formula bar—there are a bazillion of them. Once you’ve found the comma you can then grab everything to the right (or left) of it. To do that you need to subtract the position of the comma from the overall length of cell A2.

=LEN(A20)-COMMA POSITION

To compensate for the space between the  comma and the first name you add a -1 — be sure to remember the brackets because you are subtracting the 1 from the whole calculation.

=(LEN(A2)-COMMA POSITION)-1

So now you have the elements necessary to use the right formula whose syntax calls is Right(text, number of characters).

=right(A2, (length-comma position)-1) or
=RIGHT(A2,(LEN(A2)-SEARCH(“,”,A2,1))-1)

To find the last name is almost the same but using the Left formula, which has similar syntax to RIGHT. It’s a bit easier because you don’t have to subtract from the length to find the number of characters. You still have to subtract 1 to account for the comma itself.

=left(A2,position of the comma-1) or
=LEFT(A32,SEARCH(“,”,A2,1)-1)

Simple huh? I know, I know, it is complex and mind numbing. That’s why I generally figure these calcs once, store them in a master spreadsheet and then reuse them when necessary. Remember that Excel’s fill down command will allow you to repeat the calculation for as many rows as you need and will automatically update the cell numbers to accommodate you.

Putting it Back

Changing it back is even easier.

First	Last	Bill To Name
Frank	Jones	Jones, Frank

=B2 & “, ” & A2

Cell C2 is equal to cell B2 plus a comma and a space (in quotes because they are text not data) and then cell A2. Simple!