{"id":427411,"date":"2022-04-06T15:00:28","date_gmt":"2022-04-06T12:00:28","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/9-useful-microsoft-excel-functions-for-working-with-text\/"},"modified":"2022-04-06T15:00:28","modified_gmt":"2022-04-06T12:00:28","slug":"9-useful-microsoft-excel-functions-for-working-with-text","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/9-useful-microsoft-excel-functions-for-working-with-text\/","title":{"rendered":"#9 Useful Microsoft Excel Functions for Working With Text"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_85 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-6a35d519b45bb\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #dd3333;color:#dd3333\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #dd3333;color:#dd3333\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6a35d519b45bb\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/buradabiliyorum.com\/en\/9-useful-microsoft-excel-functions-for-working-with-text\/#%E2%80%9C9_Useful_Microsoft_Excel_Functions_for_Working_With_Text%E2%80%9D\" >&#8220;9 Useful Microsoft Excel Functions for Working With Text&#8221;<\/a><ul class='ez-toc-list-level-2' ><li class='ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/buradabiliyorum.com\/en\/9-useful-microsoft-excel-functions-for-working-with-text\/#Convert_the_Letter_Case_UPPER_LOWER_and_PROPER\" >Convert the Letter Case: UPPER, LOWER, and PROPER<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/buradabiliyorum.com\/en\/9-useful-microsoft-excel-functions-for-working-with-text\/#Remove_Spaces_TRIM\" >Remove Spaces: TRIM<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/buradabiliyorum.com\/en\/9-useful-microsoft-excel-functions-for-working-with-text\/#Compare_Text_Strings_EXACT\" >Compare Text Strings: EXACT<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/buradabiliyorum.com\/en\/9-useful-microsoft-excel-functions-for-working-with-text\/#Locate_Text_Within_a_String_FIND\" >Locate Text Within a String: FIND<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/buradabiliyorum.com\/en\/9-useful-microsoft-excel-functions-for-working-with-text\/#Replace_Existing_Text_Using_a_Position_REPLACE\" >Replace Existing Text Using a Position: REPLACE<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/buradabiliyorum.com\/en\/9-useful-microsoft-excel-functions-for-working-with-text\/#Substitute_Current_With_New_Text_SUBSTITUTE\" >Substitute Current With New Text: SUBSTITUTE<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/buradabiliyorum.com\/en\/9-useful-microsoft-excel-functions-for-working-with-text\/#Combine_Text_CONCAT\" >Combine Text: CONCAT<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h1><span class=\"ez-toc-section\" id=\"%E2%80%9C9_Useful_Microsoft_Excel_Functions_for_Working_With_Text%E2%80%9D\"><\/span>&#8220;9 Useful Microsoft Excel Functions for Working With Text&#8221;<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<div>\n<img loading=\"lazy\" decoding=\"async\" class=\"type:primaryImage alignnone size-full wp-image-757715\" data-pagespeed-lazy-srcset=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/microsoft_excel_hero_1200x675.jpg?width=398&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1 400w, https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/microsoft_excel_hero_1200x675.jpg?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1 1200w\" sizes=\"auto, 400w, 1200w\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/microsoft_excel_hero_1200x675.jpg?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Microsoft Excel logo on a green background\" width=\"1200\" height=\"675\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Functions in Excel aren\u2019t just for numbers and calculations. You can use functions when working with text too. Here are several helpful Microsoft Excel text functions.<\/p>\n<p>Whether you want to change the letter case, find text within another string, substitute old text with something new, or combine text from multiple cells, there\u2019s a function here for you.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>12 Basic Excel Functions Everybody Should Know<\/em><\/strong><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Convert_the_Letter_Case_UPPER_LOWER_and_PROPER\"><\/span>Convert the Letter Case: UPPER, LOWER, and PROPER<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>You may want your text to <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/164598\/how-to-quickly-and-easily-change-case-in-excel-2013-using-a-function\/\">contain all uppercase or all lowercase letters. Or maybe you want the first letter of each word capitalized. This is when the UPPER, LOWER, and PROPER functions come in handy.<\/p>\n<p>The syntax for each is the same with just one required argument:<\/p>\n<ul>\n<li><code>UPPER(cell_reference)<\/code><\/li>\n<li><code>LOWER(cell_reference)<\/code><\/li>\n<li><code>PROPER(cell_reference)<\/code><\/li>\n<\/ul>\n<p>To change the text in cell B4 to all uppercase letters, use the following formula:<\/p>\n<pre>=UPPER(B4)<\/pre>\n<p>To change the text in that same cell to all lowercase letters, use this formula instead:<\/p>\n<pre>=LOWER(B4)<\/pre>\n<p>To change the text in cell B4 to capitalize the first letter of each word, use this formula:<\/p>\n<pre>=PROPER(B4)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-791565 size-full\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/ProperFunction-ExcelTextFunctions.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"PROPER function in Excel\" width=\"520\" height=\"190\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Remove_Spaces_TRIM\"><\/span>Remove Spaces: TRIM<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>You may have extra spaces in the text that you want to remove. The <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/710718\/how-to-use-the-trim-function-in-microsoft-excel\/\">TRIM function takes care of eliminating spaces without manual work.<\/p>\n<p>The syntax for the function is <code>TRIM(text)<\/code> where you can enter the text in quotes or use a cell reference in the formula.<\/p>\n<p>To remove the spaces in the phrase \u201d\u00a0\u00a0 trim\u00a0\u00a0 spaces\u00a0\u00a0 \u201d you would use the following formula:<\/p>\n<pre>=TRIM(\"\u00a0\u00a0 trim\u00a0\u00a0 spaces\u00a0\u00a0 \")<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-791566\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/TrimText-ExcelTextFunctions.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"TRIM text in Excel\" width=\"550\" height=\"120\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>To remove the spaces in the text in cell A1, you would use the cell reference as in this formula:<\/p>\n<pre>=TRIM(A1)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-791567\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/TrimCell-ExcelTextFunctions.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"TRIM cell reference in Excel\" width=\"430\" height=\"120\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Compare_Text_Strings_EXACT\"><\/span>Compare Text Strings: EXACT<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Maybe you have two cells containing text that you want to compare and see if they <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/439286\/how-to-use-the-xlookup-function-in-microsoft-excel\/\">match exactly. Appropriately named, the EXACT function comes to the rescue.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Use the XLOOKUP Function in Microsoft Excel<\/em><\/strong><\/p>\n<p>The syntax for the function is <code>EXACT(cell_reference1, cell_reference2)<\/code> where both cell references are required. The result is True for an exact match or False for no match.<\/p>\n<p>To compare the text in cells A1 and B1, you would enter the following formula:<\/p>\n<pre>=EXACT(A1,B1)<\/pre>\n<p>In this first example, the result is True. Both text strings are identical.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-791571\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/ExactTrue-ExcelTextFunctions.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"EXACT with True result\" width=\"460\" height=\"125\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>In the second example, the result is False. The text in cell A1 has uppercase letters whereas the text in cell B1 does not.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-791572\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/ExactFalseCaps-ExcelTextFunctions.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"EXACT with False due to upper case letters\" width=\"460\" height=\"125\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>In our final example, the result is False once more. The text in cell B1 has spaces that the text in cell A1 does not.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-791573\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/ExactFalseSpaces-ExcelTextFunctions.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"EXACT with False due to spaces\" width=\"460\" height=\"125\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p><strong>RELATED:<\/strong> <strong><em>Functions vs. Formulas in Microsoft Excel: What&#8217;s the Difference?<\/em><\/strong><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Locate_Text_Within_a_String_FIND\"><\/span>Locate Text Within a String: FIND<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>If you want to find specific text within another string of text, you can use the FIND function. Keep in mind that the function is case-sensitive and does not use wildcards.<\/p>\n<p>The syntax for the function is <code>FIND(find, within, start_number)<\/code> where the first two arguments are required. The <code>start_number<\/code> argument is optional and allows you to specify with which character position to start the search.<\/p>\n<p>To find \u201cQR1\u201d within the text in cell A1, you would use this formula:<\/p>\n<pre>=FIND(\"QR1\",A1)<\/pre>\n<p>The result shown below is 8 representing the eighth character in the string as the start of the located text.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-791574\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/Find-ExcelTextFunctions.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"FIND function in Excel\" width=\"470\" height=\"125\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>To find the letter F in cell A1 beginning with the fourth character, you would use this formula:<\/p>\n<pre>=FIND(\"F\",A1,4)<\/pre>\n<p>The result here is 6 because that is the character position for first capital F after the fourth character.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-791575\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/FindInstance-ExcelTextFunctions.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"FIND with an instance\" width=\"470\" height=\"125\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Replace_Existing_Text_Using_a_Position_REPLACE\"><\/span><a rel=\"nofollow noopener\" target=\"_blank\" name=\"autotoc_anchor_4\">Replace Existing Text Using a Position: REPLACE<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>If you\u2019ve ever had to <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/442859\/how-to-find-and-replace-text-and-numbers-in-excel\/\">replace text based on where it exists in a text string, you\u2019ll appreciate the REPLACE function.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Find and Replace Text and Numbers in Excel<\/em><\/strong><\/p>\n<p>The syntax for the function is <code>REPLACE(current_text, start_number, number_characters, new_text)<\/code> where each argument is required. Let\u2019s look at the details for the arguments.<\/p>\n<ul>\n<li><code>Current_text<\/code>: The cell reference(s) for the current text.<\/li>\n<li><code>Start_number<\/code>: The first character\u2019s numeric position in the current text.<\/li>\n<li><code>Number_characters<\/code>: The number of characters you want to replace.<\/li>\n<li><code>New_text<\/code>: The new text to replace the current text.<\/li>\n<\/ul>\n<p>In this example, the first two characters of our product IDs in cells A1 through A5 are changing from \u201cID\u201d to \u201cPR.\u201d This formula would make that change in one fell swoop:<\/p>\n<pre>=REPLACE(A1:A5,1,2,\"PR\")<\/pre>\n<p>To break that down, A1:A5 is our cell range, 1 is the position of the first character to replace, 2 is the number of characters to replace, and \u201cPR\u201d is the new text.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-791577\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/ReplaceBeginning-ExcelTextFunctions.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"REPLACE text at the beginning\" width=\"540\" height=\"220\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Here\u2019s another example for that product ID. Using this formula, we can change the eighth and ninth characters in the string \u201cQR\u201d with \u201cVV.\u201d<\/p>\n<pre>=REPLACE(A1:A5,8,2,\"VV\")<\/pre>\n<p>To break this one down, <code>A1:A5<\/code> is our cell range, <code>8<\/code> is the position of the first character to replace, <code>2<\/code> is the number of characters to replace, and <code>VV<\/code> is the new text.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-791576\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/ReplaceEnd-ExcelTextFunctions.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"REPLACE text at the end\" width=\"540\" height=\"220\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Substitute_Current_With_New_Text_SUBSTITUTE\"><\/span>Substitute Current With New Text: SUBSTITUTE<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Similar to REPLACE, you can use the SUBSTITUTE function to <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/415246\/how-to-convert-text-to-date-values-in-microsoft-excel\/\">change the actual text rather than using a character\u2019s position.<\/p>\n<p>The syntax is <code>SUBSTITUTE(cell_reference, current_text, new_text, instances)<\/code> where all arguments are required except for <code>instances<\/code>. You can use <code>instances<\/code> to specify which occurrence in the text string to change.<\/p>\n<p>To change the last name Smith to Jones in cell A1, use the following formula:<\/p>\n<pre>=SUBSTITUTE(A1,\"Smith\",\"Jones\")<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-791578\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/SubstituteName-ExcelTextFunctions.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"SUBSTITUTE a name\" width=\"600\" height=\"125\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>To change \u201cLocation 1, Quarter 1\u201d to \u201cLocation 1, Quarter 2\u201d in cell A1, you would use this formula:<\/p>\n<pre>=SUBSTITUTE(A1,\"1\",\"2\",2)<\/pre>\n<p>Breaking down this formula, A1 is the cell reference, 1 is the current text, 2 is the new text, and the final number 2 is the second instance in the string. This ensures that only the second occurrence of the number 1 is changed.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-791579\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/SubstituteNumber-ExcelTextFunctions.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"SUBSTITUTE a number\" width=\"550\" height=\"125\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Combine_Text_CONCAT\"><\/span>Combine Text: CONCAT<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>One final function you may find helpful when working with text is CONCAT. This function helps you <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/279719\/how-to-combine-text-from-multiple-cells-into-one-cell-in-excel\/\">join text from multiple strings or locations into one string.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Combine Text from Multiple Cells into One Cell in Excel<\/em><\/strong><\/p>\n<p>The syntax for the function is <code>CONCAT(text1, text2)<\/code> where only the first argument is required, but you\u2019ll likely always use the second argument.<\/p>\n<p>To join the text in cells A1 and B1 with a space between the words, use this formula:<\/p>\n<pre>=CONCAT(A1,\" \",B1)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-791581\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/Concat-ExcelTextFunctions.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"CONCAT with a space\" width=\"500\" height=\"125\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Notice that the quotes contain the space to add.<\/p>\n<p>To join that same text but add the prefix Mr. and a space in front, you would use this formula:<\/p>\n<pre>=CONCAT(\"Mr. \",A1,\" \",B1)<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-791580\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/03\/ConcatAdd-ExcelTextFunctions.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"CONCAT with a prefix\" width=\"550\" height=\"125\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Here you have Mr. with a space in the first set of quotes, the first cell reference, another space within quotes, and the second cell reference.<\/p>\n<p>Hopefully these Excel text functions help you manipulate your text in less time and with less effort.<\/p>\n<\/div>\n<p><script>\n setTimeout(function(){\n  !function(f,b,e,v,n,t,s)\n  {if(f.fbq)return;n=f.fbq=function(){n.callMethod?\n  n.callMethod.apply(n,arguments):n.queue.push(arguments)};\n  if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version='2.0';\n  n.queue=[];t=b.createElement(e);t.async=!0;\n  t.src=v;s=b.getElementsByTagName(e)[0];\n  s.parentNode.insertBefore(t,s) } (window, document,'script',\n  'https:\/\/connect.facebook.net\/en_US\/fbevents.js');\n   fbq('init', '335401813750447');\n   fbq('track', 'PageView');\n  },3000);\n<\/script><\/p>\n<blockquote><p><strong><span style=\"color: #ff6600;\">If you liked the article, do not forget to share it with your friends. Follow us on\u00a0<span style=\"color: #ff0000;\"><a style=\"color: #ff0000;\" href=\"https:\/\/news.google.com\/publications\/CAAqBwgKMLG0nwswvr63Aw\" target=\"_blank\" rel=\"nofollow noopener noreferrer\">Google News<\/a><\/span>\u00a0too, click on the star and choose us from your favorites.<\/span><\/strong><\/p><\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\">For forums sites go to <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/forum.buradabiliyorum.com\/\" target=\"_blank\" rel=\"noopener\">Forum.BuradaBiliyorum.Com<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\"><strong>If you want to read more like this article, you can visit our <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/en.buradabiliyorum.com\/technology\/\" target=\"_blank\" rel=\"noopener\">Technology category.<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<p><span style=\"color: black;\"><a style=\"color: #ff9900;\" href=\"https:\/\/www.howtogeek.com\/791554\/excel-functions-for-text\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;9 Useful Microsoft Excel Functions for Working With Text&#8221; Functions in Excel aren\u2019t just for numbers and calculations. You can use functions when working with text too. Here are several helpful Microsoft Excel text functions. Whether you want to change the letter case, find text within another string, substitute old text with something new, or&#8230;<\/p>\n","protected":false},"author":1,"featured_media":427412,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/microsoft_excel_hero_1200x675.jpg?height=200p&trim=2,2,2,2","fifu_image_alt":"","footnotes":""},"categories":[18],"tags":[],"class_list":["post-427411","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technology"],"_links":{"self":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/427411","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/comments?post=427411"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/427411\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/427412"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=427411"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=427411"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=427411"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}