{"id":51,"date":"2016-01-31T00:01:10","date_gmt":"2016-01-31T00:01:10","guid":{"rendered":"http:\/\/www.heinovision.com\/useful\/?p=51"},"modified":"2016-01-31T00:03:04","modified_gmt":"2016-01-31T00:03:04","slug":"how-to-extract-a-portion-of-text-from-an-excel-cell-as-used-for-meta-tags","status":"publish","type":"post","link":"http:\/\/www.heinovision.com\/useful\/2016\/01\/how-to-extract-a-portion-of-text-from-an-excel-cell-as-used-for-meta-tags\/","title":{"rendered":"How to extract a portion of text from an Excel cell (as used for meta tags)"},"content":{"rendered":"
This is a trick I used to extract the content of a meta description tag in Excel.<\/p>\n
Let’s say you have the following in cell A1:<\/p>\n
<meta\u00a0name=\"description\"\u00a0content=\"Official dealer of wooden garden furniture.\"><\/xmp><\/code><\/p>\n<p>You can use Excel’s search and mid functions to extract the sentence “Official dealer of wooden garden furniture.” (without quotes).<\/p>\n<p>First, search for “description” by using this formula in cell B1:<\/p>\n<p><code>=SEARCH(\"description\",A1,1)<\/code><\/p>\n<p>Next, search for the closing “>” by using this formula in cell C1:<\/p>\n<p><code>=SEARCH(\">\",A1,B1) <\/code><\/p>\n<p>Finally, use the mid function to extract the sentence by using this formula in cell D1:<\/p>\n<p><code>=MID(A1,B1+22,C1-B1-23)<\/code><\/p>\n<p>Extra tip: The reverse of this is recreating the code in Excel using the append function (&). Cell A1 would be:<\/p>\n<p><code><xmp><meta\u00a0name=\"description\"\u00a0content=\"<\/xmp><\/code><\/p>\n<p>Cell B1 would be:<\/p>\n<p><code>Official dealer of wooden garden furniture.<\/code><\/p>\n<p>Cell C1 would be:<\/p>\n<p><code><xmp>\"><\/xmp><\/code><\/p>\n<p>Finally, cell D1 would tie it all together:<\/p>\n<p><code>=A1&B1&C1<\/code><\/p>\n<p>You can copy and paste as values to clean up the end result.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This is a trick I used to extract the content of a meta description tag in Excel. Let’s say you have the following in cell A1: You can use Excel’s search and mid functions to extract the sentence “Official dealer of wooden garden furniture.” (without quotes). First, search for “description”<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[4],"tags":[],"_links":{"self":[{"href":"http:\/\/www.heinovision.com\/useful\/wp-json\/wp\/v2\/posts\/51"}],"collection":[{"href":"http:\/\/www.heinovision.com\/useful\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.heinovision.com\/useful\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.heinovision.com\/useful\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.heinovision.com\/useful\/wp-json\/wp\/v2\/comments?post=51"}],"version-history":[{"count":6,"href":"http:\/\/www.heinovision.com\/useful\/wp-json\/wp\/v2\/posts\/51\/revisions"}],"predecessor-version":[{"id":58,"href":"http:\/\/www.heinovision.com\/useful\/wp-json\/wp\/v2\/posts\/51\/revisions\/58"}],"wp:attachment":[{"href":"http:\/\/www.heinovision.com\/useful\/wp-json\/wp\/v2\/media?parent=51"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.heinovision.com\/useful\/wp-json\/wp\/v2\/categories?post=51"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.heinovision.com\/useful\/wp-json\/wp\/v2\/tags?post=51"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}