{"id":3429,"date":"2017-11-29T10:10:16","date_gmt":"2017-11-29T16:10:16","guid":{"rendered":"https:\/\/www.vertex42.com\/blog\/?p=3429"},"modified":"2024-12-06T13:57:53","modified_gmt":"2024-12-06T19:57:53","slug":"text-formulas-in-excel","status":"publish","type":"post","link":"https:\/\/www.vertex42.com\/blog\/excel-formulas\/text-formulas-in-excel.html","title":{"rendered":"Text Manipulation Formulas in Excel &#8211; The Ultimate Guide"},"content":{"rendered":"<div class=\"feature-image center aligncenter\" itemprop=\"image\" itemscope itemtype=\"https:\/\/schema.org\/ImageObject\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/feature\/text-formulas-in-excel-730x382.png\" alt=\"Text Formulas in Excel\" width=\"730\" height=\"382\" class=\"center imgshadow\"><meta itemprop=\"url\" content=\"https:\/\/cdn.vertex42.com\/blog\/images\/feature\/text-formulas-in-excel-730x382.png\" \/><meta itemprop=\"width\" content=\"730\" \/><meta itemprop=\"height\" content=\"382\" \/><\/div>\n<p>When you are cleaning up data in Excel, the Text-to-Columns and Flash Fill features are awesome, but sometimes you need to use formulas to manipulate text. In this article I'll demonstrate examples of the text formulas I commonly use, including LEN, TRIM, UPPER, LOWER, PROPER, CONCATENATE, INDIRECT, CHAR, FIND, SEARCH, SUBSTITUTE, LEFT, RIGHT, MID and REPLACE (and some others).<\/p>\n<p>The examples start very simple and then get progressively more advanced as you scroll through the page, building upon earlier examples.<\/p>\n<p class=\"downloadlink\"><span class=\"icon16 excel\"><\/span><a href=\"\/Files\/examples\/TextFormulas.xlsx\" onClick=\"ga('send','event', 'Downloads', 'Examples', 'TextFormulas.xlsx');\" rel=\"nofollow\"><b>Download the Example File<\/b><\/a> (TextFormulas.xlsx)<\/p>\n<p><b>Do you have a text manipulation challenge that you need a formula to solve?<\/b> Feel free to ask your question by commenting below.<\/p>\n<div class=\"clear\"><\/div>\n<div class=\"contents\">\n<p>Links to Text Formulas on this Page<\/p>\n<ul>\n<li>1. <a href=\"#len\" class=\"bm\">Get the LENgth of a text string<\/a><\/li>\n<li>2. <a href=\"#case\" class=\"bm\">Change case to UPPER, lower, or Proper<\/a><\/li>\n<li>3. <a href=\"#concatenate\" class=\"bm\">CONCATENATE a text string<\/a><\/li>\n<li>4. <a href=\"#indirect\" class=\"bm\">Use INDIRECT to create a reference from a text string<\/a><\/li>\n<li>5. <a href=\"#char\" class=\"bm\">Use CHAR to return special characters<\/a><\/li>\n<li>6. <a href=\"#substitute\" class=\"bm\">SUBSTITUTE text within a string<\/a><\/li>\n<li>7. <a href=\"#trim\" class=\"bm\">Use TRIM to get rid of extra spaces<\/a><\/li>\n<li>8. <a href=\"#find\" class=\"bm\">Use FIND and SEARCH to get the position of text in a string<\/a><\/li>\n<li>9a. <a href=\"#mid\" class=\"bm\">Use MID, LEFT and RIGHT to extract text from a string<\/a><\/li>\n<li>9b. <a href=\"#textafter\" class=\"bm\">New TEXTAFTER, TEXTBEFORE functions<\/a><\/li>\n<li>10. <a href=\"#count-spaces\" class=\"bm\">Count the number of spaces in a string<\/a><\/li>\n<li>11. <a href=\"#count-occurrences\" class=\"bm\">Count occurrences of a string within text<\/a><\/li>\n<li>12. <a href=\"#split\" class=\"bm\">Split text into columns using formulas<\/a><\/li>\n<li>13. <a href=\"#last-word\" class=\"bm\">Get the last word in a string<\/a><\/li>\n<li>14. <a href=\"#nth-word\" class=\"bm\">Get the Nth word in a string<\/a><\/li>\n<li>15. <a href=\"#string-to-array\" class=\"bm\">Convert a string to an array of words<\/a><\/li>\n<li>16. <a href=\"#string-to-characters\" class=\"bm\">Convert a string to an array of characters<\/a><\/li>\n<li>17. <a href=\"#exact\" class=\"bm\">Use EXACT for case-sensitive text comparisons<\/a><\/li>\n<li>18. <a href=\"#sequence\" class=\"bm\">Create a SEQUENCE of characters<\/a><\/li>\n<li>19. <a href=\"#regex\" class=\"bm\">New REGEX functions!<\/a><\/li>\n<\/ul>\n<\/div>\n<div class=\"clear\"><\/div>\n<h2 id=\"len\">1. Get the LENgth of a text string<\/h2>\n<pre class=\"XLformula light\">=<span class=\"function\">LEN<\/span>(<span class=\"green\">\"onetwothree\"<\/span>)\r\nResult: 11<\/pre>\n<p>This comes in handy when you need to write a title for a web page or complete a form with a limited number of characters. Just open a blank spreadsheet and type your title in cell A1. In B1, enter =LEN(A1).<\/p>\n<h2 id=\"case\">2. Change case to UPPER, lower, or Proper<\/h2>\n<pre class=\"XLformula light\">=<span class=\"function\">UPPER<\/span>(<span class=\"green\">\"this text\"<\/span>)   Result: THIS TEXT\r\n=<span class=\"function\">LOWER<\/span>(<span class=\"green\">\"THIS TEXT\"<\/span>)   Result: this text\r\n=<span class=\"function\">PROPER<\/span>(<span class=\"green\">\"this text\"<\/span>)   Result: This Text<\/pre>\n<h2 id=\"concatenate\">3. Concatenate a text string<\/h2>\n<p>You can use <b>CONCATENATE<\/b>, the <b>&amp;<\/b> operator, or the newer <b>CONCAT<\/b> and <b>TEXTJOIN<\/b> functions to concatenate strings. The following formulas combine a first name in cell A1 and a last name in cell B1 with a space in the middle. The result is \"John Smith\" for all four formulas.<\/p>\n<pre class=\"XLformula light\">\r\nA1=<span class=\"green\">\"John\"<\/span>\r\nB1=<span class=\"green\">\"Smith\"<\/span>\r\n\r\n=<span class=\"green\">A1<\/span> <b>&amp;<\/b> <span class=\"green\">\" \"<\/span> <b>&amp;<\/b> <span class=\"green\">B1<\/span>\r\n=<span class=\"function\">CONCATENATE<\/span>(<span class=\"green\">A1<\/span>,<span class=\"green\">\" \"<\/span>,<span class=\"green\">B1<\/span>)\r\n=<span class=\"function\">CONCAT<\/span>(<span class=\"green\">A1:B1<\/span>)\r\n=<span class=\"function\">TEXTJOIN<\/span>(<span class=\"green\">\" \"<\/span>,TRUE,<span class=\"green\">A1:B1<\/span>)\r\n\r\nResult: \"John Smith\"\r\n<\/pre>\n<div class=\"note-box\">\n<p><span class=\"note-label\">NOTE<\/span> The spaces before and after the <b>&amp;<\/b> operator are not required - I've included the spaces only to help make the formula more readable.<\/p>\n<\/div>\n<p>The <a href=\"https:\/\/support.office.com\/en-us\/article\/CONCAT-function-9b1a9a3f-94ff-41af-9736-694cbd6b4ca2\" target=\"_blank\" rel=\"noopener noreferrer\">CONCAT<\/a> and <a href=\"https:\/\/support.office.com\/en-us\/article\/TEXTJOIN-function-357b449a-ec91-49d0-80c3-0e8fc845691c\" target=\"_blank\" rel=\"noopener noreferrer\">TEXTJOIN<\/a> functions are new functions that requires an Office 365 subscription (they work in Excel Online). The CONCAT function is like CONCATENATE except that it lets you use a range of cells as an argument. The TEXTJOIN function lets you specify a delimiter and ignore blank values.<\/p>\n<h2 id=\"indirect\">4. Use INDIRECT to create a reference from a text string<\/h2>\n<p>The <b>INDIRECT<\/b> function allows you to create a reference from a text string. The example below shows a reference to cell A5 in worksheet 'Sheet 2'. The single quotes around the worksheet name are only necessary if the worksheet name includes a space.<\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">INDIRECT<\/span>(<span class=\"green\">\"'Sheet 2'!A5\"<\/span>)<\/pre>\n<p>Use INDIRECT if you want the worksheet name to be a text string chosen by the user. For example, you may want to do this if you have many identical worksheets and you want to create a summary table that uses the names of those worksheets as references in your lookup formulas.<\/p>\n<p>The following example creates a reference to cell X5 in a worksheet that is named in cell A1.<\/p>\n<pre class=\"XLformula light\">\r\nA1=<span class=\"green\">\"Sheet 2\"<\/span>\r\nA2=<span class=\"function\">INDIRECT<\/span>(<span class=\"green\">\"'\"<\/span> &amp; <span class=\"green\">A1<\/span> &amp; <span class=\"green\">\"'!X5\"<\/span>)\r\n<\/pre>\n<p>The INDIRECT function can be very useful in <a href=\"\/blog\/excel-formulas\/array-formula-examples.html\">array formulas<\/a>. For example, to create an array of numbers 1 through N, where N is a number contained in cell A1, you can use:<\/p>\n<pre class=\"XLformula light\">\r\n=<span class=\"function\">ROW<\/span>(<span class=\"function\">INDIRECT<\/span>(<span class=\"green\">\"1:\"<\/span> &amp; <span class=\"green\">A1<\/span>))\r\n<\/pre>\n<h2 id=\"char\">5. Use CHAR to return special characters<\/h2>\n<p>The <b>CHAR<\/b> function lets you return a character for a given numeric code. The <b>UNICHAR<\/b> function returns a character for a decimal Unicode value. Although most of the numeric codes for the CHAR function correspond to the ASCII codes, some may not be the same (such as codes 128-160).<\/p>\n<p>The functions <b>CODE<\/b> and <b>UNICODE<\/b> are the opposites of CHAR and UNICHAR, returning the numeric value for the first character in a text string.<\/p>\n<h3>Use CHAR(34) to return the double quote &quot; character<\/h3>\n<p>When you concatenate text and need to include double quotes in the displayed text, you can use the CHAR(34) or UNICHAR(34) function. Both the ASCII and Unicode value for double quotes is 34.<\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">CHAR<\/span>(<span class=\"green\">34<\/span>) &amp; <span class=\"green\">\"Hi World\"<\/span> &amp; <span class=\"function\">CHAR<\/span>(<span class=\"green\">34<\/span>)\r\nResult: \"Hi World\" (quotes included)<\/pre>\n<h3 id=\"linebreak\">Use CHAR(10) to include a line break in a string<\/h3>\n<p>When using a formula to return a string, use CHAR(10) or UNICHAR(10) for a line break. See <a href=\"\/blog\/excel-tips\/custom-number-formats-in-excel.html\">Custom Number Formats<\/a> to learn how to add a line break within a custom number format (for chart labels and stuff like that).<\/p>\n<pre class=\"XLformula light\">=<span class=\"green\">\"abc\"<\/span> &amp; <span class=\"function\">CHAR<\/span>(<span class=\"green\">10<\/span>) &amp; <span class=\"green\">\"def\"<\/span>\r\nResult:\r\nabc\r\ndef\r\n<\/pre>\n<div class=\"caution-box\">\n<p><span class=\"caution-label\">!<\/span> To display wrapped text with line breaks, the cell must also have the <b>Word Wrap<\/b> property toggled <b>on<\/b>.<\/p>\n<\/div>\n<div class=\"note-box\">\n<p><span class=\"note-label\">TIP<\/span> To quickly generate a list of characters based on their numeric code, enter =CHAR(ROW()) or =UNICHAR(ROW()) into cell A1 of a blank worksheet and copy the formula down.<\/p>\n<\/div>\n<p>See my article \"<a href=\"https:\/\/www.vertex42.com\/blog\/help\/excel-help\/using-unicode-character-symbols-in-excel.html\">Using UNICODE Characters in Excel<\/a>\" for more information.<\/p>\n<h2 id=\"substitute\">6. SUBSTITUTE text within a string<\/h2>\n<p>The <b>SUBSTITUTE<\/b> function is very powerful. It can be used to replace ether ALL occurrences or just the Nth occurrence of a string with another character or text string. In the example below, we're replacing the # character with a space.<\/p>\n<pre class=\"XLformula light\">\r\n<span class=\"arg\">text<\/span> = \"one#two#three\"\r\n=<span class=\"function\">SUBSTITUTE<\/span>(<span class=\"arg\">text<\/span>,<span class=\"green\">\"#\"<\/span>,<span class=\"green\">\" \"<\/span>)   Result: \"one two three\"\r\n=<span class=\"function\">SUBSTITUTE<\/span>(<span class=\"arg\">text<\/span>,<span class=\"green\">\"#\"<\/span>,<span class=\"green\">\" \"<\/span>,<span class=\"green\">2<\/span>)   Result: \"one#two three\"\r\n<\/pre>\n<h2 id=\"trim\">7. Use TRIM to get rid of extra spaces<\/h2>\n<p>The <b>TRIM<\/b> function removes all regular spaces (ASCII character 32) except for a single space between words. TRIM(num) can also be a useful way to convert a number to text without any special formatting, or to wrap a formula to make sure that the result of a text formula remains text.<\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">TRIM<\/span>(<span class=\"green\">\"   Hi  World  \"<\/span>)\r\nResult: \"Hi World\"  (quotes not included)<\/pre>\n<p>TRIM does not remove tabs, line breaks, or other nonprinting characters from the text. To remove the non-printing ASCII characters 0-31 (including the tab character), you can use the <b>CLEAN<\/b> function.<\/p>\n<pre class=\"XLformula light\">\r\n<span class=\"arg\">text<\/span>=\"Hi\t\tWorld\" (contains two tabs)\r\n=<span class=\"function\">CLEAN<\/span>(<span class=\"arg\">text<\/span>)\r\nResult: \"HiWorld\"<\/pre>\n<p>The problem with the CLEAN function is that it completely removes the characters, so words separated by tabs or newline characters will be combined, so you may end up with \"HiWorld\" when you would prefer \"Hi World\".<\/p>\n<p>To change special characters to regular spaces, you can use the <b>SUBSTITUTE<\/b> function and then wrap the function with TRIM to remove extra spaces like this:<\/p>\n<pre class=\"XLformula light\">\r\n<span class=\"arg\">text<\/span>=\"Hi\t\tWorld\" (contains two tabs)\r\n=<span class=\"function\">TRIM<\/span>( <span class=\"function\">SUBSTITUTE<\/span>(<span class=\"arg\">text<\/span>,<span class=\"function\">CHAR<\/span>(9),\" \") )\r\nResult: \"Hi World\"<\/pre>\n<div class=\"note-box\">\n<p><span class=\"note-label\">NOTE<\/span> Here is a short list of CHAR codes for commonly replaced characters: Tab (9), Newline (10), Carriage Return (13), Space (32), Non-Breaking Space (160), Special Quote Symbols: &#8216;(145), &#8217;(146), &#8220;(147), &#8221;(148)<\/p>\n<\/div>\n<h3>Two LAMBDAs for Special Clean Functions<\/h3>\n<p>The following lambda function first changes tabs (9), newlines (10), and non-breaking spaces (160) to regular spaces, then uses CLEAN to remove other nonprinting characters, and finally TRIM to remove extra spaces.<\/p>\n<pre class=\"XLformula light lambda\">\r\n<span class=\"purple\">textCleanPlus<\/span> =<span class=\"function\">LAMBDA<\/span><span class=\"purple\">(<\/span><span class=\"arg\">text<\/span>,\r\nTRIM(CLEAN(SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(text,CHAR(160),\" \"),CHAR(10),\" \"),CHAR(9),\" \") ))\r\n<span class=\"purple\">)<\/span>\r\n<\/pre>\n<p>The following lambda function replaces the special single quote (145) and (146) with a regular single quote and special double quotes (147) and (148) with regular double quotes.<\/p>\n<pre class=\"XLformula light lambda\">\r\n<span class=\"purple\">textFixSpecialQuotes<\/span> =<span class=\"function\">LAMBDA<\/span><span class=\"purple\">(<\/span><span class=\"arg\">text<\/span>,\r\nSUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(text,CHAR(145),\"'\"),CHAR(146),\"'\"), CHAR(147),CHAR(34)), CHAR(148),CHAR(34))\r\n<span class=\"purple\">)<\/span>\r\n<\/pre>\n<h2 id=\"find\">8. Use FIND and SEARCH to get the position of text in a string<\/h2>\n<p>The <b>FIND<\/b> function for case-sensitive searching and the <b>SEARCH<\/b> function for case-insensitive searching will return the starting character position of a text string within another string.<\/p>\n<pre class=\"XLformula light\">\r\n=<span class=\"function\">FIND<\/span>(<span class=\"green\">\"a\"<\/span>,\"oo<span style=\"color:darkgreen;font-weight:bold;\">A<\/span>oo<span style=\"color:darkgreen;font-weight:bold;\">a<\/span>oo\",1)   Result: 6\r\n=<span class=\"function\">SEARCH<\/span>(<span class=\"green\">\"a\"<\/span>,\"oo<span style=\"color:darkgreen;font-weight:bold;\">A<\/span>oo<span style=\"color:darkgreen;font-weight:bold;\">a<\/span>oo\",1)   Result: 3\r\n<\/pre>\n<p>The 3rd argument of the FIND and SEARCH functions is the starting character position to begin the search, with the default being 1 (the first character). You can use a nested FIND or nested SEARCH to find the position of the 2nd occurrence of a text string like this:<\/p>\n<pre class=\"XLformula light\">\r\n<span class=\"arg\">text<\/span>=<span class=\"green\">\"ooAooAoo\"<\/span>\r\n=<span class=\"function\">FIND<\/span>(<span class=\"green\">\"A\"<\/span>,<span class=\"arg\">text<\/span>,<span class=\"function\">FIND<\/span>(<span class=\"green\">\"A\"<\/span>,<span class=\"arg\">text<\/span>,1)+1)\r\n\r\nEvaluation Steps\r\nStep 1: <span class=\"function\">FIND<\/span>(\"A\",\"ooAooAoo\",<span class=\"green\">3<\/span>+1)\r\nStep 2: <span class=\"function\">FIND<\/span>(\"A\",\"ooAooAoo\",<span class=\"green\">4<\/span>)\r\nStep 3: <span class=\"green\">6<\/span><\/pre>\n<p>You can do some really tricky things with SUBSTITUTE when combined with FIND or SEARCH. The following function allows you to find the location of the Nth occurrence of a string within another text string. In this example, we want to know the position of the 3rd space in the name.<\/p>\n<pre class=\"XLformula light\"><span class=\"arg\">text<\/span>=\"Tim A. J. Crane\"\r\n=<span class=\"function\">FIND<\/span>(<span class=\"green\">\"#\"<\/span>,<span class=\"function\">SUBSTITUTE<\/span>(<span class=\"arg\">text<\/span>,<span class=\"green\">\" \"<\/span>,<span class=\"green\">\"#\"<\/span>,<span class=\"green\">3<\/span>),1)\r\n\r\nEvaluation Steps\r\nStep 1: =FIND(\"#\",<span class=\"green\">\"Tim A. J.#Crane\"<\/span>,1)\r\nStep 2: =<span class=\"green\">10<\/span>\r\n<\/pre>\n<h2 id=\"mid\">9a. Use MID, LEFT and RIGHT to extract text from a string<\/h2>\n<p>The <b>MID<\/b> function is like the substr() function in other coding languages. It extracts a string from within another string by specifying the starting character position and the number of characters to extract. The <b>REPLACE<\/b> function is similar except that it returns the original text string with the text replaced. The <b>LEFT<\/b> and <b>RIGHT<\/b> functions are like shorthand versions of MID for extracting text from the left or right end of a string.<\/p>\n<pre class=\"XLformula light\">SYNTAX: =<span class=\"function\">MID<\/span>(<span class=\"arg\">text<\/span>,<span class=\"arg\">start_num<\/span>,<span class=\"arg\">num_chars<\/span>)\r\nSYNTAX: =<span class=\"function\">REPLACE<\/span>(<span class=\"arg\">text<\/span>,<span class=\"arg\">start_num<\/span>,<span class=\"arg\">num_chars<\/span>,<span class=\"arg\">replace_text<\/span>)\r\nSYNTAX: =<span class=\"function\">LEFT<\/span>(<span class=\"arg\">text<\/span>,<span class=\"arg\">num_chars<\/span>)\r\nSYNTAX: =<span class=\"function\">RIGHT<\/span>(<span class=\"arg\">text<\/span>,<span class=\"arg\">num_chars<\/span>)<\/pre>\n<p>Below is an example showing how these functions work.<\/p>\n<pre class=\"XLformula light\">\r\n<span class=\"arg\">text<\/span> = <span class=\"green\">\"one#two#three\"<\/span>\r\n=<span class=\"function\">MID<\/span>(<span class=\"arg\">text<\/span>,<span class=\"green\">5<\/span>,<span class=\"green\">3<\/span>)   Result: \"two\"\r\n=<span class=\"function\">REPLACE<\/span>(<span class=\"arg\">text<\/span>,<span class=\"green\">5<\/span>,<span class=\"green\">3<\/span>,\"BLAHBLAH\")   Result: \"one#BLAHBLAH#three\"\r\n=<span class=\"function\">LEFT<\/span>(<span class=\"arg\">text<\/span>,<span class=\"green\">5<\/span>)   Result: \"one#t\"\r\n=<span class=\"function\">RIGHT<\/span>(<span class=\"arg\">text<\/span>,<span class=\"green\">7<\/span>)   Result: \"o#three\"\r\n<\/pre>\n<p>I haven't had much use for the REPLACE function, because I typically use SUBSTITUTE instead of REPLACE.<\/p>\n<p>The <b>MID<\/b>, <b>LEFT<\/b>, and <b>RIGHT<\/b> functions become much more powerful when you use the <b>FIND<\/b> or <b>SEARCH<\/b> functions within them. Some of the following tips show examples of that.<\/p>\n<h2 id=\"textafter\">9b. Use the New TEXTAFTER and TEXTBEFORE functions!<\/h2>\n<p>New text functions are coming soon to the latest version of Excel. Like their names suggest, TEXTAFTER and TEXTBEFORE let you extract text <em>after<\/em> or <em>before<\/em> the <strong><em>n<\/em><\/strong>th instance of a delimiter. Some of the complex stuff done with the MID function can be done more easily with these new functions.<\/p>\n<pre class=\"XLformula light\">SYNTAX: =<span class=\"function\">TEXTAFTER<\/span>(<span class=\"arg\">text<\/span>,<span class=\"arg\">delimiter<\/span>,[<span class=\"arg\">n<\/span>],[<span class=\"arg\">ignore_case<\/span>])\r\nSYNTAX: =<span class=\"function\">TEXTBEFORE<\/span>(<span class=\"arg\">text<\/span>,<span class=\"arg\">delimiter<\/span>,[<span class=\"arg\">n<\/span>],[<span class=\"arg\">ignore_case<\/span>])\r\n<\/pre>\n<p>Using the text from the MID function above, here are a couple examples:<\/p>\n<pre class=\"XLformula light\">\r\n<span class=\"arg\">text<\/span> = <span class=\"green\">\"one#two#three\"<\/span>\r\n=<span class=\"function\">TEXTAFTER<\/span>(<span class=\"arg\">text<\/span>,<span class=\"green\">\"#\"<\/span>,<span class=\"green\">2<\/span>)   Result: \"three\"\r\n=<span class=\"function\">TEXTBEFORE<\/span>(<span class=\"arg\">text<\/span>,<span class=\"green\">\"#\"<\/span>,<span class=\"green\">2<\/span>)   Result: \"one#two\"\r\n<\/pre>\n<p>If you enter a negative value for <strong><em>n<\/em><\/strong>, it will count the instance from the end of the string. This will come in handy when you need to extract the last part of the string but you don't know how many delimiters there will be.<\/p>\n<h2 id=\"count-spaces\">10. Count the number of spaces in a text string<\/h2>\n<p>See the <a href=\"https:\/\/www.vertex42.com\/lambda\/countchar.html\"><strong>L_COUNTCHAR<\/strong><\/a> function in the <a href=\"https:\/\/www.vertex42.com\/lambda\/\">Vertex42 Lambda Library<\/a>!<\/p>\n<p>You can use this technique to count other characters besides spaces. For example, just substitute \" \" with \",\" or \";\" to count the number of commas or semi-colons.<\/p>\n<pre class=\"XLformula light\">\r\n<span class=\"arg\">text<\/span> = <span class=\"green\">\"Todd Allen Smith\"<\/span>\r\n=<span class=\"function\">LEN<\/span>(<span class=\"arg\">text<\/span>)-<span class=\"function\">LEN<\/span>(<span class=\"function\">SUBSTITUTE<\/span>(<span class=\"arg\">text<\/span>,<span class=\"green\">\" \"<\/span>,\"\"))\r\n\r\nEvaluation Steps\r\nStep 1: LEN(<span class=\"green\">\"Todd Allen Smith\"<\/span>)-LEN(<span class=\"green\">\"ToddAllenSmith\"<\/span>)\r\nStep 2: <span class=\"green\">16<\/span>-<span class=\"green\">14<\/span>\r\nStep 3: <span class=\"green\">2<\/span>\r\n<\/pre>\n<p>The SUBSTITUTE function in this example returns a new text string with the spaces removed (replacing all \" \" with \"\"). We are subtracting the length of that modified text string from the original length to calculate the number of spaces in the original text.<\/p>\n<h2 id=\"count-occurrences\">11. Count occurrences of a string within text<\/h2>\n<p>If you want to count the number of occurrences of a string within text (instead of just a single character), then you can use a slightly modified version of the above formula. In this case, we'll just divide the result by the length of string.<\/p>\n<pre class=\"XLformula light\"><span class=\"arg\">text<\/span> = <span class=\"green\">\"A##B##C\"<\/span>\r\n<span class=\"arg\">string<\/span> = <span class=\"green\">\"##\"<\/span>\r\n=(<span class=\"function\">LEN<\/span>(<span class=\"arg\">text<\/span>)-<span class=\"function\">LEN<\/span>(<span class=\"function\">SUBSTITUTE<\/span>(<span class=\"arg\">text<\/span>,<span class=\"arg\">string<\/span>,\"\"))) \/ <span class=\"function\">LEN<\/span>(<span class=\"arg\">string<\/span>)\r\nResult: 2<\/pre>\n<p>If you find yourself doing this a lot, you can create a LAMBDA function to return the count of instances of <i>str<\/i> within <i>text<\/i>.<\/p>\n<p>See the <a href=\"https:\/\/www.vertex42.com\/lambda\/countchar.html\"><strong>L_COUNTCHAR<\/strong><\/a> function in the <a href=\"https:\/\/www.vertex42.com\/lambda\/\">Vertex42 Lambda Library<\/a>!<\/p>\n<h2 id=\"split\">12. Split text into columns using formulas<\/h2>\n<p>The Text-to-Columns Wizard and Flash Fill (Ctrl+e) features in Excel are fast and simple to use, but there may be times when you want to use formulas instead (to make a more dynamic or automated worksheet). Splitting up text using formulas typically involves a combination of LEFT, RIGHT, MID, LEN, and FIND (or SEARCH). We'll start with a couple simple formulas.<\/p>\n<h3>Extract the First Name<\/h3>\n<p>To extract the first word (or name) from a text string, you can use the following formula, where text is either a cell reference or a string surrounded by double quotes like \"this\".<\/p>\n<pre class=\"XLformula light\">\r\n<span class=\"arg\">text<\/span> = \"Tom Sawyer\"\r\n=<span class=\"function\">LEFT<\/span>(<span class=\"arg\">text<\/span>,<span class=\"function\">FIND<\/span>(\" \",<span class=\"arg\">text<\/span>)-1)\r\n\r\nEvaluation Steps\r\nStep 1: =LEFT(<span class=\"green\">\"Tom Sawyer\"<\/span>,<span class=\"green\">4<\/span>-1)\r\nStep 2: =LEFT(<span class=\"green\">\"Tom Sawyer\"<\/span>,<span class=\"green\">3<\/span>)\r\nStep 3: =<span class=\"green\">\"Tom\"<\/span><\/pre>\n<p>In the above formula, FIND(\" \",<i>text<\/i>) returns the numeric position of the first space \" \" within the text. We subtract one from that value so the space is not included in the result.<\/p>\n<p>The new TEXTBEFORE function is easier (the default for the instance number is 1):<\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">TEXTBEFORE<\/span>(<span class=\"green\">\"Tom Sawyer\"<\/span>,<span class=\"green\">\" \"<\/span>)  Result: \"Tom\"\r\n<\/pre>\n<p>The new REGEX<strong>EXTRACT<\/strong> function harnesses the power of regular expressions to extract text. Here is a regular expression to return the first word (i.e. name):<\/p>\n<pre class=\"XLformula light\">\r\ntext = \"Tom Sawyer\"\r\n=<span class=\"function\">REGEXEXTRACT<\/span>(<span class=\"arg\">text<\/span>,\"<span class=\"green\">^\\w+<\/span>\")\r\nResult: \"Tom\"\r\n<\/pre>\n<h3>Extract the Text After the First Space<\/h3>\n<p>To return the rest of the string after the first space, we use the <b>RIGHT<\/b> function, which extracts a specified number of characters from the end of the string. We calculate the number of characters to extract by subtracting the position of space from the total length of the string:<\/p>\n<pre class=\"XLformula light\">\r\n<span class=\"arg\">text<\/span> = \"Jay Allen Reems\"\r\n=<span class=\"function\">RIGHT<\/span>(<span class=\"arg\">text<\/span>,<span class=\"function\">LEN<\/span>(<span class=\"arg\">text<\/span>)-<span class=\"function\">FIND<\/span>(\" \",<span class=\"arg\">text<\/span>))\r\n\r\nEvaluation Steps\r\nStep 1: =RIGHT(\"Jay Allen Reems\",LEN(\"Jay Allen Reems\")-<span class=\"green\">4<\/span>)\r\nStep 2: =RIGHT(\"Jay Allen Reems\",<span class=\"green\">15<\/span>-4)\r\nStep 3: =RIGHT(\"Jay Allen Reems\",<span class=\"green\">11<\/span>)\r\nStep 4: =<span class=\"green\">\"Allen Reems\"<\/span>\r\n<\/pre>\n<p>The new TEXTAFTER function is easier (the default for the instance number is 1):<\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">TEXTAFTER<\/span>(<span class=\"green\">\"Jay Allen Reems\"<\/span>,<span class=\"green\">\" \"<\/span>)  Result: \"Allen Reems\"\r\n<\/pre>\n<p>Although TEXTAFTER is easier, here is a way to do it using REGEX<strong>EXTRACT<\/strong>:<\/p>\n<pre class=\"XLformula light\">\r\n=<span class=\"function\">REGEXEXTRACT<\/span>(\"Todd Allen Smith\", \"<span class=\"green\"> (?<= )(.+)<\/span>\")\r\nResult: \"Allen Smith\"\r\n<\/pre>\n<p>We could repeat these formulas in other columns to extract Allen and then Reems.<\/p>\n<p>The article \"<a href=\"https:\/\/support.office.com\/en-us\/article\/Split-text-into-different-columns-with-functions-49ec57f9-3d5a-44b2-82da-50dded6e4a68\" target=\"_blank\" rel=\"noopener noreferrer\">Split text into different columns with functions<\/a>\" on support.office.com provides various examples of formulas for separating names into different parts based on different ways that a name may be written.<\/p>\n<h3>The SPLIT function in Google Sheets<\/h3>\n<p>I hope that Excel eventually includes a SPLIT function like the one available in Google Sheets. For example, to split a name like \"Allen James Reems\" into separate cells only requires the following simple formula:<\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">SPLIT<\/span>(<span class=\"arg\">text<\/span>,\" \")<\/pre>\n<h3>New TEXTSPLIT function in Excel!<\/h3>\n<p>The new TEXTSPLIT function is available in Microsoft 365. It isn't exactly the same as the Google Sheets SPLIT function. It's better in my opinion because it can split text into a row, column, or two-dimensional dynamic array.<\/p>\n<pre class=\"XLformula light\">SYNTAX: =<span class=\"function\">TEXTSPLIT<\/span>(<span class=\"arg\">text<\/span>,[<span class=\"arg\">column_delimiter<\/span>],[<span class=\"arg\">row_delimiter<\/span>],[<span class=\"arg\">ignore_empty<\/span>],[<span class=\"arg\">pad_width<\/span>])\r\n<\/pre>\n<p>You can read more about TEXTSPLIT at <a href=\"https:\/\/www.myonlinetraininghub.com\/textsplit-textbefore-and-textafter-functions\" target=\"_blank\" rel=\"noopener\">MyOnlineTrainingHub.com<\/a>.<\/p>\n<h2 id=\"last-word\">13. Get the last word in a string<\/h2>\n<p>For this example, we'll use the name \"Allen Jay Reems\" to show how to get the last word in a string, where a space character is the delimiter.<\/p>\n<p><p>In Microsoft 365, this is very easy to do with the TEXTAFTER function (<a href=\"#textafter\">see 9b above<\/a>):<\/p>\n<p><pre class=\"XLformula light\">=<span class=\"function\">TEXTAFTER<\/span>(\"Allen Jay Reems\",\" \",-1)   Result: \"Reems\"\r\n<\/pre>\n<p>Although TEXTAFTER is easier, here is a way to do it using REGEX<strong>EXTRACT<\/strong>:<\/p>\n<pre class=\"XLformula light\">\r\n=<span class=\"function\">REGEXEXTRACT<\/span>(\"Todd Allen Smith\", \"<span class=\"green\">\\b\\w+$<\/span>\")\r\nResult: \"Smith\"\r\n<\/pre>\n<p>The following example shows how to do this if TEXTAFTER is not available, and also shows how I sometimes build a more complicated formula using intermediate steps.<\/p>\n<pre class=\"XLformula light\">\r\n<span class=\"arg\">delimiter<\/span> = <span class=\"green\">\" \"<\/span>\r\n<span class=\"arg\">last_name<\/span>\r\n   =<span class=\"function\">RIGHT<\/span>(<span class=\"arg\">text<\/span>,<span class=\"function\">LEN<\/span>(<span class=\"arg\">text<\/span>)-<span class=\"arg\">position_of_last_delimiter<\/span>)\r\n<span class=\"arg\">position_of_last_delimiter<\/span>\r\n   =<span class=\"function\">FIND<\/span>(<span class=\"green\">\"^\"<\/span>, <span class=\"function\">SUBSTITUTE<\/span>(<span class=\"arg\">text<\/span>,<span class=\"arg\">delimiter<\/span>,<span class=\"green\">\"^\"<\/span>,<span class=\"arg\">number_of_delimiters<\/span>))\r\n<span class=\"arg\">number_of_delimiters<\/span>\r\n   =<span class=\"function\">LEN<\/span>(<span class=\"arg\">text<\/span>)-<span class=\"function\">LEN<\/span>(<span class=\"function\">SUBSTITUTE<\/span>(<span class=\"arg\">text<\/span>,<span class=\"arg\">delimiter<\/span>,<span class=\"green\">\"\"<\/span>))\r\n<\/pre>\n<p>The final formula looks like this with A1=\"Allen Jay Reems\" and will return the last name \"Reems\":<\/p>\n<pre class=\"XLformula light\">\r\n=<span class=\"function\">RIGHT<\/span>(<span class=\"arg\">A1<\/span>,<span class=\"function\">LEN<\/span>(<span class=\"arg\">A1<\/span>)-<span class=\"function\">FIND<\/span>(\"^\", <span class=\"function\">SUBSTITUTE<\/span>(<span class=\"arg\">A1<\/span>,\" \",\"^\",<span class=\"function\">LEN<\/span>(<span class=\"arg\">A1<\/span>)-<span class=\"function\">LEN<\/span>( <span class=\"function\">SUBSTITUTE<\/span>(<span class=\"arg\">A1<\/span>,\" \",\"\") ))))\r\n<\/pre>\n<p>If you have a string delimited by commas like \"one, two, three, four\" you can extract the last element by replacing \" \" with \",\" in the above formula and wrapping the entire thing with <b>TRIM<\/b> to remove the leading space.<\/p>\n<p>If your string might not contain any spaces, then you can wrap the entire formula with IFERROR to return an empty string or the original text.<\/p>\n<p>If your string contains the \"^\" character, you'll need to choose a different temporary delimiter to use in the formula such as \"~\" or another uncommonly used character.<\/p>\n<h2 id=\"nth-word\">14. Get the Nth word in a string<\/h2>\n<p>This is a task that is much simpler with the new TEXTSPLIT function. TEXTSPLIT will convert a text string into an array based on a delimiter. INDEX can then pick the nth value.<\/p>\n<p><pre class=\"XLformula light\">\r\n=<span class=\"function\">INDEX<\/span>(<span class=\"function\">TEXTSPLIT<\/span>(<span class=\"arg\">text<\/span>,,<span class=\"arg\">delimiter<\/span>),<span class=\"arg\">n<\/span>)\r\n=<span class=\"function\">INDEX<\/span>(<span class=\"function\">TEXTSPLIT<\/span>(<span class=\"green\">\"One#Two#Three\"<\/span>,,<span class=\"green\">\"#\"<\/span>),<span class=\"green\">2<\/span>)\r\n=<span class=\"green\">\"Two\"<\/span>\r\n<\/pre>\n<p>Without the new functions, this formula is really crazy, but still useful. I learned it from a <a href=\"http:\/\/www.mrexcel.com\/forum\/excel-questions\/587534-text-columns-via-formula.html\" target=\"_blank\" rel=\"noopener noreferrer\">post on mrexcel.com<\/a>. Basically what is going on is that you replace the delimiter text with a bunch of blank spaces so that you create a new text string that can be divided into chunks, where each chunk contains a different word. There will be a lot of space surrounding each word, so you use TRIM to remove it.<\/p>\n<pre class=\"XLformula light\">\r\n<span class=\"arg\">text<\/span> = \"One#Two#Three\" (the original text)\r\n<span class=\"arg\">delimiter<\/span> = \"#\" (the delimiter text)\r\n<span class=\"arg\">word_num<\/span> = 2 (the word to extract)\r\n\r\n=<span class=\"function\">TRIM<\/span>(<span class=\"function\">MID<\/span>(<span class=\"function\">SUBSTITUTE<\/span>(<span class=\"arg\">text<\/span>,<span class=\"arg\">delimeter<\/span>,<span class=\"function\">REPT<\/span>(\" \",<span class=\"function\">LEN<\/span>(<span class=\"arg\">text<\/span>))),(<span class=\"arg\">word_num<\/span>-1)*<span class=\"function\">LEN<\/span>(<span class=\"arg\">text<\/span>)+1,<span class=\"function\">LEN<\/span>(<span class=\"arg\">text<\/span>)))\r\n\r\nEvaluation Steps\r\n1: =TRIM(MID(SUBSTITUTE(<i>text<\/i>,<span class=\"green\">\"#\"<\/span>,REPT(\" \",<span class=\"green\">13<\/span>)),(<span class=\"green\">2<\/span>-1)*<span class=\"green\">13<\/span>+1,<span class=\"green\">13<\/span>))\r\n2: =TRIM(MID(SUBSTITUTE(<i>text<\/i>,\"#\",<span class=\"green\">\"             \"<\/span>),<span class=\"green\">14<\/span>,13))\r\n3: =TRIM(MID(<span class=\"green\">\"One             Two             Three\"<\/span>,14,13))\r\n4: =TRIM(<span class=\"green\">\"  Two        \"<\/span>)\r\n5: =<span class=\"green\">\"Two\"<\/span>\r\n<\/pre>\n<p>In Google Sheets, this is a piece of cake. The SPLIT function returns an array, so you can return the 3rd word in a string using:<\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">INDEX<\/span>(<span class=\"function\">SPLIT<\/span>(<span class=\"arg\">text<\/span>,<span class=\"arg\">delimiter<\/span>),<span class=\"green\">3<\/span>)<\/pre>\n<h2 id=\"string-to-array\">15. Convert a text string to an array of words<\/h2>\n<p>Want to convert \"One#Two#Three\" into an array like {\"One\";\"Two\";\"Three\"} that can be used within other formulas? That is what the SPLIT function in Google Sheets does, but to do this in Excel is still possible - it's just complicated. First, start with the formula in the previous section and replace <em>word_num<\/em> with the following:<\/p>\n<pre class=\"XLformula light\">\r\n=<span class=\"function\">ROW<\/span>(<span class=\"function\">INDIRECT<\/span>(\"1:\"&amp;((<span class=\"function\">LEN<\/span>(<span class=\"arg\">text<\/span>)-<span class=\"function\">LEN<\/span>(<span class=\"function\">SUBSTITUTE<\/span>(<span class=\"arg\">text<\/span>,<span class=\"arg\">delimiter<\/span>,\"\")))\/<span class=\"function\">LEN<\/span>(<span class=\"arg\">delimiter<\/span>)+1)))\r\n<\/pre>\n<p>To display the results within an array of cells, remember to use Ctrl+Shift+Enter. Use TRANSPOSE if you want to display the results of this formula in a row instead of a column.<\/p>\n<p>To create the array as an inline text string, you can use the following formula:<\/p>\n<pre class=\"XLformula light\">\r\n<span class=\"arg\">text<\/span> = \"One#Two#Three\" (the original text)\r\n<span class=\"arg\">str<\/span> = \"#\" (the delimiter text)\r\n=\"{\"&amp;<span class=\"function\">CHAR<\/span>(34)&amp;<span class=\"function\">SUBSTITUTE<\/span>(<span class=\"arg\">text<\/span>,<span class=\"arg\">str<\/span>,<span class=\"function\">CHAR<\/span>(34)&amp;\";\"&amp;<span class=\"function\">CHAR<\/span>(34))&amp;<span class=\"function\">CHAR<\/span>(34)&amp;\"}\"\r\n\r\nResulting text string: {\"One\";\"Two\";\"Three\"}\r\n<\/pre>\n<h2 id=\"string-to-characters\">16. Convert a text string to an array of characters<\/h2>\n<p>See the <a href=\"https:\/\/www.vertex42.com\/lambda\/text2array.html\"><strong>L_TEXT2ARRAY<\/strong><\/a> function in the <a href=\"https:\/\/www.vertex42.com\/lambda\/\">Vertex42 Lambda Library<\/a>!<\/p>\n<p>If you want to split a text string into an array of individual characters, such as converting \"abcd\" to {\"a\";\"b\";\"c\";\"d\"}, the formula is fairly simple. This formula is entered as an Array Formula (Ctrl+Shift+Enter).<\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">MID<\/span>(<span class=\"arg\">text_string<\/span>,<span class=\"function\">ROW<\/span>( <span class=\"function\">INDIRECT<\/span>(\"1:\"&amp;<span class=\"function\">LEN<\/span>(<span class=\"arg\">text_string<\/span>)) ),1)<\/pre>\n<p>To convert each of the characters in a string to their numeric codes, wrap the above function with CODE or UNICODE. The formula would be entered as a multi-cell array to display each of the numeric values in a different cell.<\/p>\n<p>The following formula in Google Sheets will convert a text string to a comma-delimited list of numeric code values.<\/p>\n<pre class=\"XLformula light\"><span class=\"arg\">text<\/span>=<span class=\"green\">\"Hello\"<\/span>\r\n=<span class=\"function\">ARRAYFORMULA<\/span>( <span class=\"function\">TEXTJOIN<\/span>(<span class=\"green\">\",\"<\/span>,<span class=\"green\">TRUE<\/span>, <span class=\"function\">CODE<\/span>(<span class=\"function\">MID<\/span>(<span class=\"arg\">text<\/span>,<span class=\"function\">ROW<\/span>( <span class=\"function\">INDIRECT<\/span>(\"1:\"&amp;<span class=\"function\">LEN<\/span>(<span class=\"arg\">text<\/span>)) ),1))) )\r\nResulting text string: \"72,101,108,108,111\"\r\n<\/pre>\n<h2 id=\"exact\">17. Use EXACT for case-sensitive text comparisons<\/h2>\n<p>If you ever need to determine if the text in a cell is <strong>UPPERCASE<\/strong>, <strong>lowercase<\/strong>, or <strong>Proper Case<\/strong>, you can use the EXACT formula to compare the original text to the converted text.<\/p>\n<p>The following formulas return TRUE if the text in cell A1 is uppercase, lowercase or proper case, respectively:<\/p>\n<pre class=\"XLformula light\">=<span class=\"function\">EXACT<\/span>(A1,<span class=\"function\">UPPER<\/span>(A1))\r\n=<span class=\"function\">EXACT<\/span>(A1,<span class=\"function\">LOWER<\/span>(A1))\r\n=<span class=\"function\">EXACT<\/span>(A1,<span class=\"function\">PROPER<\/span>(A1))<\/pre>\n<p>Note: The <a href=\"https:\/\/www.vertex42.com\/blog\/excel-formulas\/sumif-and-countif-in-excel.html\">SUMIF and COUNTIF<\/a> article provides a lot of different examples of text-based comparisons.<\/p>\n<h2 id=\"sequence\">18. Create a SEQUENCE of characters starting with a letter<\/h2>\n<p>See the <a href=\"https:\/\/www.vertex42.com\/lambda\/se.html\"><strong>L_SE<\/strong><\/a> function in the <a href=\"https:\/\/www.vertex42.com\/lambda\/\">Vertex42 Lambda Library<\/a>!<\/p>\n<pre class=\"XLformula light\" style=\"background-color:#F0ECF9;border:solid 3px #C5B6E7;\">\r\n=<span class=\"function\">L_SE<\/span>(\"c\",\"f\")\r\nResulting array: \"c\";\"d\";\"e\";\"f\"\r\n<\/pre>\n<p>Using the new <b>SEQUENCE<\/b> function in Office 365 makes it easy to create a sequence or array of characters because you first convert the starting character to its unicode value using CODE or UNICODE, then create the sequence, then convert that sequence back to characters using CHAR or UNICHAR.<\/p>\n<pre class=\"XLformula light\">\r\nIn Office 365!\r\n=<span class=\"function\">CHAR<\/span>( <span class=\"function\" style=\"color:#704AC4;\">SEQUENCE<\/span>(<span class=\"green\">5<\/span>,,<span class=\"function\">CODE<\/span>(<span class=\"green\">\"C\"<\/span>)) )\r\nResulting array: \"C\",\"D\",\"E\",\"F\",\"G\"\r\n<\/pre>\n<h2 id=\"regex\">19. New Regular Expression Functions in Excel!<\/h2>\n<p>The new REGEXTEST, REGEXREPLACE, and REGEXEXTRACT functions are extremely powerful for text manipulation tasks. Here are a couple of simple examples. If you are like me and always forget the syntax for regular expressions, then let ChatGPT or CoPilot figure it out for you.<\/p>\n<p><strong>Example AI Prompt<\/strong>: <em>How do I use REGEXREPLACE in Excel to remove all non-alphanumeric characters from text, except for spaces and periods?<\/em><\/p>\n<pre class=\"XLformula light\">\r\ntext = \"Hello, World #42.\"\r\n=<span class=\"function\">REGEXREPLACE<\/span>(<i>text<\/i>,\"<strong>[^a-zA-Z0-9. ]<\/strong>\",\"\")\r\nResult: \"Hello World 42.\"\r\n<\/pre>\n<p>Replace all but the last 4 digits with \"*\"<\/p>\n<pre class=\"XLformula light\">\r\ntext = \"123 45 6789\"\r\n=<span class=\"function\">REGEXREPLACE<\/span>(<i>text<\/i>,\"<strong>.(?=.{4})<\/strong>\",\"*\")\r\nResult: \"*******6789\"\r\n<\/pre>\n<h2>Have a Text Formula Challenge?<\/h2>\n<p>If this article hasn't answered your question, feel free to comment below if you have a problem that you want solved using a text formula. Make sure to provide sufficient detail for your question to be answered. Thank you!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This is the ultimate guide to using Text Formulas in Excel such as SUBSTITUTE, LEN, CHAR, CONCAT, TRIM, LEFT, RIGHT, MID, FIND, SEARCH and others. The examples start simple and then build up to more advanced formulas including cleaning up data, splitting text into columns, and converting strings to arrays.<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_links_to":"","_links_to_target":""},"categories":[79],"tags":[],"class_list":{"0":"post-3429","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-excel-formulas"},"_links":{"self":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts\/3429","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/comments?post=3429"}],"version-history":[{"count":0,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts\/3429\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/media?parent=3429"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/categories?post=3429"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/tags?post=3429"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}