Text Functions

Download

HTML can be useful for transferring content to other online publishing platforms, typically by copy/pasting to the new platform.

Excel includes functions for textual strings to reformat, separate, combine, and rewrite the text. These functions can be combined together to do advanced actions when working with textual strings.
In this chapter, we will reference data in Figure 32.1 that includes a City, State, Zip Code text string, and Population and Rank data. However, we only need the former text string.
<div class="callout default" data-callout-custom-class="default">
<h3 id="h3_TbjM" name="h3_TbjM">Practice Spreadsheet</h3>
Use this <a href="https://drive.google.com/uc?export=download&id=1C6QjirTgkiY7Gei9rSQSAAYzWA2sog-3" data-shortened-url="https://books.byui.edu/-hJyG" data-unshortened-url="https://drive.google.com/uc?export=download&id=1C6QjirTgkiY7Gei9rSQSAAYzWA2sog-3">workbook</a> for the chapter.
</div>
<figure class="image text-center"><img class="none" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_32.1.png" alt="BUS115_image_32.1.png" width="492" height="351" id="img_ZNTf" name="img_ZNTf">
<figcaption class="text-center">Figure 32.1</figcaption>
</figure>
For ease of reference, it is recommended to freeze our top row(s) containing headers and the left column(s) containing the City, State, Zip Code information. We can do this by selecting the cell below the header row and right of the aforementioned column, then going to the View tab of the ribbon toolbar, and selecting Freeze Panes and Freeze Panes again.
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_32.2.png" alt="BUS115_image_32.2.png" id="img_mmCc" name="img_mmCc">
<figcaption class="text-center">Figure 32.2</figcaption>
</figure>
<div class="clearfix"> </div>
<h2 id="h2_CTRb" name="h2_CTRb">Lower</h2>
The Lower function is used to edit a text string to have all lowercase letters. In this example, we will use it to lowercase all letters in the city, state, and zip code information cell.
<ol id="ol_FXJc" name="ol_FXJc">
<li id="li_kJoU" aria-level="1" name="li_kJoU">Type =LOWER and a left parenthesis ( ( ) to start the function.</li>
<li id="li_nBNE" aria-level="1" name="li_nBNE">Select the cell containing the City, State, Zip Code data.</li>
<li id="li_iDKk" aria-level="1" name="li_iDKk">Type a right parenthesis ( ) ) to close the formula.</li>
<li id="li_vdUa" aria-level="1" name="li_vdUa">Press Enter to complete the function. (See Figure 32.3)</li>
</ol>
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_32.3.png" alt="BUS115_image_32.3.png" id="img_oInr" name="img_oInr">
<figcaption class="text-center">Figure 32.3</figcaption>
</figure>
<div class="clearfix"> </div>
<h2 id="h2_Pjvf" name="h2_Pjvf">Upper</h2>
The Upper function is used to edit a text string to have all uppercase letters. We can use it to uppercase all letters in the city, state, and zip code information cell.
<ol id="ol_dgmB" name="ol_dgmB">
<li id="li_JEqs" aria-level="1" name="li_JEqs">Type =UPPER and a left parenthesis ( ( ) to start the function.</li>
<li id="li_UKQD" aria-level="1" name="li_UKQD">Select the cell containing the City, State, Zip Code data.</li>
<li id="li_Yqqn" aria-level="1" name="li_Yqqn">Type a right parenthesis ( ) ) to close the formula.</li>
<li id="li_pYMG" aria-level="1" name="li_pYMG">Press Enter to complete the function. (See Figure 32.4)</li>
</ol>
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_32.4.png" alt="BUS115_image_32.4.png" id="img_pYgD" name="img_pYgD">
<figcaption class="text-center">Figure 32.4</figcaption>
</figure>
<div class="clearfix"> </div>
<h2 id="h2_ytdK" name="h2_ytdK">Proper</h2>
The Proper function is used to edit a text string to uppercase the first letter of each word. Our original reference data already has words formatted in this manner, but we can use one of the previously completed Lower or Upper function cells for reference.
<ol id="ol_cmif" name="ol_cmif">
<li id="li_epqw" aria-level="1" name="li_epqw">Type =PROPER and a left parenthesis ( ( ) to start the function.</li>
<li id="li_WDoZ" aria-level="1" name="li_WDoZ">Select one of the previous function cells containing all lower or upper case text.</li>
<li id="li_Qvzq" aria-level="1" name="li_Qvzq">Type a right parenthesis ( ) ) to close the formula.</li>
<li id="li_yMmy" aria-level="1" name="li_yMmy">Press Enter to complete the function. (See Figure 32.5)</li>
</ol>
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_32.5.png" alt="BUS115_image_32.5.png" id="img_EmYZ" name="img_EmYZ">
<figcaption class="text-center">Figure 32.5</figcaption>
</figure>
<div class="clearfix"> </div>
<h2 id="h2_NhZZ" name="h2_NhZZ">Left</h2>
The Left function can be used to pull and display a designated number of characters beginning from the left of a text string.
<ol id="ol_pIbL" name="ol_pIbL">
<li id="li_qwxC" aria-level="1" name="li_qwxC">Type =LEFT and a left parenthesis ( ( ) to start the function.</li>
<li id="li_jpiA" aria-level="1" name="li_jpiA">Select the cell containing the City, State, Zip Code data and type a comma ( , ).</li>
<li id="li_aitV" aria-level="1" name="li_aitV">Type a preferred number of characters to pull from the selected cell.</li>
<ol id="ol_SmPj" style="list-style-type: lower-alpha;" name="ol_SmPj">
<li id="li_YJsj" aria-level="2" name="li_YJsj">In this example, we'll pull three characters from the text string of the selected cell.</li>
</ol>
<li id="li_DDqA" aria-level="1" name="li_DDqA">Type a right parenthesis ( ) ) to close the formula.</li>
<li id="li_YIYD" aria-level="1" name="li_YIYD">Press Enter to complete the function. (See Figure 32.6)</li>
</ol>
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_32.6.png" alt="BUS115_image_32.6.png" id="img_bvNU" name="img_bvNU">
<figcaption class="text-center">Figure 32.6</figcaption>
</figure>
<div class="clearfix"> </div>
<h2 id="h2_aQrM" name="h2_aQrM">Right</h2>
Identical to the Left function, the Right function can be used to pull and display a designated number of textual characters beginning from the right of a text string. For this example, we'll pull five characters to copy the zip code at the end of the text string.
<ol id="ol_vvif" name="ol_vvif">
<li id="li_QbdG" aria-level="1" name="li_QbdG">Type =RIGHT and a left parenthesis ( ( ) to start the function.</li>
<li id="li_KRdr" aria-level="1" name="li_KRdr">Select the cell containing the City, State, Zip Code data and type a comma ( , ).</li>
<li id="li_CRiD" aria-level="1" name="li_CRiD">Type a preferred number of characters to pull from the selected cell.</li>
<ol id="ol_MLjD" style="list-style-type: lower-alpha;" name="ol_MLjD">
<li id="li_DJnj" aria-level="2" name="li_DJnj">In this example, we'll pull five characters from the text string of the selected cell.</li>
</ol>
<li id="li_sdtf" aria-level="1" name="li_sdtf">Type a right parenthesis ( ) ) to close the formula.</li>
<li id="li_pnhQ" aria-level="1" name="li_pnhQ">Press Enter to complete the function. (See Figure 32.7)</li>
</ol>
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_32.7.png" alt="BUS115_image_32.7.png" id="img_JIoK" name="img_JIoK">
<figcaption class="text-center">Figure 32.7</figcaption>
</figure>
<div class="clearfix"> </div>
<h2 id="h2_dknK" name="h2_dknK">Length</h2>
The Length function counts the number of characters in a cell's text string.
<ol id="ol_XAzz" name="ol_XAzz">
<li id="li_bUTv" aria-level="1" name="li_bUTv">Type =LEN and a left parenthesis ( ( ) to start the function.</li>
<li id="li_ZXir" aria-level="1" name="li_ZXir">Select the preferred cell to evaluate the number of characters it contains.</li>
<li id="li_psVM" aria-level="1" name="li_psVM">Type a right parenthesis ( ) ) to close the formula.</li>
<li id="li_mWjS" aria-level="1" name="li_mWjS">Press Enter to complete the function. (See Figure 32.8)</li>
</ol>
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_32.8.png" alt="BUS115_image_32.8.png" id="img_CaoT" name="img_CaoT">
<figcaption class="text-center">Figure 32.8</figcaption>
</figure>
<div class="clearfix"> </div>
<h2 id="h2_QgYh" name="h2_QgYh">Trim</h2>
The Trim function removes any extra spaces found within the cell's text string. The data for city, state, and zip code includes two spaces between state and zip code information. We can use Trim to remove the second space.
<ol id="ol_eYke" name="ol_eYke">
<li id="li_oTNG" aria-level="1" name="li_oTNG">Type =TRIM and a left parenthesis ( ( ) to start the function.</li>
<li id="li_jSUa" aria-level="1" name="li_jSUa">Select the cell containing the City, State, Zip Code data.</li>
<li id="li_cHzd" aria-level="1" name="li_cHzd">Type a right parenthesis ( ) ) to close the formula.</li>
<li id="li_ixGg" aria-level="1" name="li_ixGg">Press Enter to complete the function. (See Figure 32.9)</li>
</ol>
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_32.9.png" alt="BUS115_image_32.9.png" id="img_JVRh" name="img_JVRh">
<figcaption class="text-center">Figure 32.9</figcaption>
</figure>
<div class="clearfix"> </div>
<h2 id="h2_uWdN" name="h2_uWdN">Ampersand</h2>
An ampersand symbol instructs Excel to combine two references in an argument.
<ol id="ol_nivD" name="ol_nivD">
<li id="li_kTgQ" aria-level="1" name="li_kTgQ">Type an equals sign ( = ).</li>
<li id="li_oQYw" aria-level="1" name="li_oQYw">Select the first cell to be combined and type an ampersand ( & ).</li>
<li id="li_qwdZ" aria-level="1" name="li_qwdZ">Type " " (with a space between the quotation marks).</li>
<li id="li_kfft" aria-level="1" name="li_kfft">Type an ampersand ( & ) and select the second cell to be combined.</li>
<li id="li_InAG" aria-level="1" name="li_InAG">Press Enter to complete the formula. (See Figure 32.10)</li>
</ol>
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_32.10.png" alt="BUS115_image_32.10.png" id="img_mfyK" name="img_mfyK">
<figcaption class="text-center">Figure 32.10</figcaption>
</figure>
<div class="clearfix"> </div>
<h2 id="h2_HqDP" name="h2_HqDP">Concatenate</h2>
Similar in function to the ampersand, the CONCAT (Concatenate) function combines multiple cell references into one cell.
<ol id="ol_ubMd" name="ol_ubMd">
<li id="li_oUaL" aria-level="1" name="li_oUaL">Type =CONCAT and a left parenthesis ( ( ) to start the function.</li>
<li id="li_IvKM" aria-level="1" name="li_IvKM">Select the first cell to be combined and type a comma ( , ).</li>
<li id="li_Reuw" aria-level="1" name="li_Reuw">Select a second cell to be combined.</li>
<ol id="ol_PbWd" style="list-style-type: lower-alpha;" name="ol_PbWd">
<li id="li_crtu" aria-level="2" name="li_crtu">Additional cells may be combined by typing another comma and selecting the next cell. </li>
</ol>
<li id="li_emeU" aria-level="1" name="li_emeU">Type a right parenthesis ( ) ) to close the formula.</li>
<li id="li_ceoM" aria-level="1" name="li_ceoM">Press Enter to complete the function. (See Figure 32.11)</li>
</ol>
Note: Like the previous function example, you may need to include a space between quotation marks to separate the selected items. For example, the Figure 32.11 formula would look like the following: =CONCAT(M3," ",K3," ",J3)
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_32.11.png" alt="BUS115_image_32.11.png" id="img_RECd" name="img_RECd">
<figcaption class="text-center">Figure 32.11</figcaption>
</figure>
<div class="clearfix"> </div>
<h2 id="h2_TMPi" name="h2_TMPi">Value</h2>
The Value function converts numbers used in a textual string format to number format. In the case of the zip code produced in the earlier Right function section, we'll use Value combined with Right to convert the zip code into a numerical format whereby it will be right-aligned in the cell.
<ol id="ol_LvJK" name="ol_LvJK">
<li id="li_fXRA" aria-level="1" name="li_fXRA">Type =VALUE and a left parenthesis ( ( ) to start the function.</li>
<li id="li_BwQA" aria-level="1" name="li_BwQA">Type RIGHT and a second left parenthesis ( ( ).</li>
<li id="li_RgdT" aria-level="1" name="li_RgdT">Select the previous Right function cell containing the zip code data.</li>
<li id="li_pIXy" aria-level="1" name="li_pIXy">Type two right parenthesis ( ) ) to close both formulas.</li>
<li id="li_Ixit" aria-level="1" name="li_Ixit">Press Enter to complete the function. (See Figure 32.12)</li>
</ol>
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_32.12.png" alt="BUS115_image_32.12.png" id="img_zNmK" name="img_zNmK">
<figcaption class="text-center">Figure 32.12</figcaption>
</figure>
<div class="clearfix"> </div>
<h2 id="h2_gDnC" name="h2_gDnC">Find</h2>
The Find function is used to locate the first position of an indicated character in a referenced textual string. Note: This function is case-sensitive.
<ol id="ol_dEvW" name="ol_dEvW">
<li id="li_dxcq" aria-level="1" name="li_dxcq">Type =FIND and a left parenthesis ( ( ) to start the function.</li>
<li id="li_YIEn" aria-level="1" name="li_YIEn">Type "," and a comma ( , ).</li>
<li id="li_cbVn" aria-level="1" name="li_cbVn">Select the cell containing the City, State, Zip Code data.</li>
<li id="li_eTar" aria-level="1" name="li_eTar">Type a right parenthesis ( ) ) to close the formula.</li>
<li id="li_DgSz" aria-level="1" name="li_DgSz">Press Enter to complete the function. (See Figure 32.13)</li>
</ol>
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_32.13.png" alt="BUS115_image_32.13.png" id="img_iGmD" name="img_iGmD">
<figcaption class="text-center">Figure 32.13</figcaption>
</figure>
<div class="clearfix"> </div>
<h2 id="h2_FNEW" name="h2_FNEW">Search</h2>
Identical to the Find function, the Search function will locate the first position of an indicated character in a text string. Note: This function is not case-sensitive.
<ol id="ol_UQNR" name="ol_UQNR">
<li id="li_QBZI" aria-level="1" name="li_QBZI">Type =SEARCH and a left parenthesis ( ( ) to start the function.</li>
<li id="li_Ewyv" aria-level="1" name="li_Ewyv">Type "a" and a comma ( , ).</li>
<li id="li_opKx" aria-level="1" name="li_opKx">Select the cell containing the City, State, Zip Code data.</li>
<li id="li_goeK" aria-level="1" name="li_goeK">Type a right parenthesis ( ) ) to close the formula.</li>
<li id="li_UCUW" aria-level="1" name="li_UCUW">Press Enter to complete the function. (See Figure 32.14)</li>
</ol>
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_32.14.png" alt="BUS115_image_32.14.png" id="img_bFnQ" name="img_bFnQ">
<figcaption class="text-center">Figure 32.14</figcaption>
</figure>
<div class="clearfix"> </div>
<h2 id="h2_wcNT" name="h2_wcNT">Replace</h2>
The Replace function is capable of rewriting a designated portion of the referenced cell's text. In this example, we'll reference the city, state, and zip code cell, indicate a starting point, determine the number of characters from that point to be replaced, and insert a new string.
<ol id="ol_LViz" name="ol_LViz">
<li id="li_ptXn" aria-level="1" name="li_ptXn">Type =REPLACE and a left parenthesis ( ( ) to start the function.</li>
<li id="li_kgkL" aria-level="1" name="li_kgkL">Select the cell containing the City, State, Zip Code data and type a comma ( , ).</li>
<li id="li_Suqj" aria-level="1" name="li_Suqj">Type 1 to indicate the beginning of the text string.</li>
<li id="li_VDse" aria-level="1" name="li_VDse">Type a comma ( , ).</li>
<li id="li_FMKA" aria-level="1" name="li_FMKA">Type 10 or another number to indicate the number of characters to replace.</li>
<li id="li_SrbH" aria-level="1" name="li_SrbH">Type the new text enclosed in double quotation marks.</li>
<ol id="ol_AEMM" style="list-style-type: lower-alpha;" name="ol_AEMM">
<li id="li_kpEh" aria-level="2" name="li_kpEh">In this example, we will use "Example" for the new text.</li>
</ol>
<li id="li_uabT" aria-level="1" name="li_uabT">Type a right parenthesis ( ) ) to close the formula.</li>
<li id="li_TDds" aria-level="1" name="li_TDds">Press Enter to complete the function. (See Figure 32.15)</li>
</ol>
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_32.15.png" alt="BUS115_image_32.15.png" id="img_rJKi" name="img_rJKi">
<figcaption class="text-center">Figure 32.15</figcaption>
</figure>
<div class="clearfix"> </div>
<h2 id="h2_RccJ" name="h2_RccJ">Substitute</h2>
Substitute works similarly to the Replace function by searching for a designated character or text to be substituted and inserting the new text.
<ol id="ol_vfYj" name="ol_vfYj">
<li id="li_EeUE" aria-level="1" name="li_EeUE">Type =SUBSTITUTE and a left parenthesis ( ( ) to start the function.</li>
<li id="li_eNUL" aria-level="1" name="li_eNUL">Select the cell containing the City, State, Zip Code data and type a comma ( , ).</li>
<li id="li_SNUy" aria-level="1" name="li_SNUy">Type "a" and a comma ( , ).</li>
<li id="li_YvUp" aria-level="1" name="li_YvUp">Type "_" to indicate the new character.</li>
<li id="li_uJRC" aria-level="1" name="li_uJRC">Type a right parenthesis ( ) ) to close the formula.</li>
<li id="li_mGQu" aria-level="1" name="li_mGQu">Press Enter to complete the function. (See Figure 32.16)</li>
</ol>
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_32.16.png" alt="BUS115_image_32.16.png" id="img_DNIu" name="img_DNIu">
<figcaption class="text-center">Figure 32.16</figcaption>
</figure>
<div class="clearfix"> </div>
<h2 id="h2_WIZi" name="h2_WIZi">Middle</h2>
The Middle function works like the Left and Right functions by beginning at a specified position in a text string and pulling a determined number of characters.
<ol id="ol_PKLU" name="ol_PKLU">
<li id="li_bhFN" aria-level="1" name="li_bhFN">Type =MID and a left parenthesis ( ( ) to start the function.</li>
<li id="li_dLTH" aria-level="1" name="li_dLTH">Select the cell containing the City, State, Zip Code data and type a comma ( , ).</li>
<li id="li_bDsm" aria-level="1" name="li_bDsm">Type the preferred number of characters to indicate the start point and a comma ( , ).</li>
<li id="li_iVLb" aria-level="1" name="li_iVLb">Type the preferred number of characters to pull from the selected cell.</li>
<li id="li_VCnP" aria-level="1" name="li_VCnP">Type a right parenthesis ( ) ) to close the formula.</li>
<li id="li_dBiV" aria-level="1" name="li_dBiV">Press Enter to complete the function. (See Figure 32.17)</li>
</ol>
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_32.17.png" alt="BUS115_image_32.17.png" id="img_rgEH" name="img_rgEH">
<figcaption class="text-center">Figure 32.17</figcaption>
</figure>
<div class="clearfix"> </div>
Now, we want to extract individual data from the City, State, Zip Code column. We will start by finding the city using the =LEFT function, then the state using the =MID function, and finally, the zip code using the =RIGHT function.
<h2 id="h2_EQpT" name="h2_EQpT">Combining Functions</h2>
The City column will be our first step. In this column, we’ll be using the =LEFT function. Use the following steps to extrapolate the city from the City, State, Zip Code column:, 
<ol id="ol_osSd" name="ol_osSd">
<li id="li_LHhX" aria-level="1" name="li_LHhX">Type =LEFT and a left parenthesis ( ( ) to start the function.</li>
<li id="li_khhg" aria-level="1" name="li_khhg">Select the cell containing the City, State, Zip Code data and type a comma ( , ).</li>
<li id="li_ioZj" aria-level="1" name="li_ioZj">Type FIND and a second left parenthesis ( ( ).</li>
<li id="li_wPzy" aria-level="1" name="li_wPzy">Type "," and a comma ( , ).</li>
<li id="li_TiVn" aria-level="1" name="li_TiVn">Select the same cell containing City, State, Zip Code data.</li>
<li id="li_ZXFy" aria-level="1" name="li_ZXFy">Type a right parenthesis ( ) ) to close the FIND formula.</li>
<li id="li_cnJk" aria-level="1" name="li_cnJk">Type -1 or another number to subtract extra characters as needed.</li>
<li id="li_FMJN" aria-level="1" name="li_FMJN">Type a right parenthesis ( ) ) to close the LEFT formula.</li>
<li id="li_FciK" aria-level="1" name="li_FciK">Press Enter to complete the function. (See Figure 32.18)</li>
</ol>
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_32.18.png" alt="BUS115_image_32.18.png" id="img_RCZh" name="img_RCZh">
<figcaption class="text-center">Figure 32.18</figcaption>
</figure>
<div class="clearfix"> </div>
The State column is a little trickier. In this column, we’ll be using the =MID function. Use the following steps to extrapolate the state from the City, State, Zip Code column:
<ol id="ol_MQAF" name="ol_MQAF">
<li id="li_IRUi" aria-level="1" name="li_IRUi">Type =MID and a left parenthesis ( ( ) to start the function.</li>
<li id="li_Saup" aria-level="1" name="li_Saup">Select the cell containing the City, State, Zip Code data and type a comma ( , ).</li>
<li id="li_ifvf" aria-level="1" name="li_ifvf">Type LEN and a second left parenthesis ( ( ).</li>
<li id="li_EoLk" aria-level="1" name="li_EoLk">Select the previous cell containing only the city name.</li>
<li id="li_QIZN" aria-level="1" name="li_QIZN">Type a right parenthesis ( ) ) to close the LEN formula.</li>
<li id="li_xrCw" aria-level="1" name="li_xrCw">Type +3 or another number to add extra characters as needed.</li>
<li id="li_VHdp" aria-level="1" name="li_VHdp">Type a comma ( , ).</li>
<li id="li_jRwk" aria-level="1" name="li_jRwk">Type FIND and a left parenthesis ( ( ).</li>
<li id="li_DhPw" aria-level="1" name="li_DhPw">Type "  " (with two spaces between the quotation marks) and a comma ( , ).</li>
<li id="li_vJsm" aria-level="1" name="li_vJsm">Select the same cell containing City, State, Zip Code data.</li>
<li id="li_GVwk" aria-level="1" name="li_GVwk">Type a right parenthesis ( ) ) to close the FIND formula.</li>
<li id="li_tkWE" aria-level="1" name="li_tkWE">Type -LEN and a left parenthesis ( ( ).</li>
<li id="li_Ktrq" aria-level="1" name="li_Ktrq">Select the previous cell containing only the city name.</li>
<li id="li_aoxM" aria-level="1" name="li_aoxM">Type a right parenthesis ( ) ) to close the LEN function.</li>
<li id="li_AYNT" aria-level="1" name="li_AYNT">Type -3 or another number to subtract extra characters as needed.</li>
<li id="li_vZCs" aria-level="1" name="li_vZCs">Type a right parenthesis ( ) ) to close the MID formula.</li>
<li id="li_DLDP" aria-level="1" name="li_DLDP">Press Enter to complete the function. (See Figure 32.19)</li>
</ol>
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_32.19.png" alt="BUS115_image_32.19.png" id="img_ZIkg" name="img_ZIkg">
<figcaption class="text-center">Figure 32.19</figcaption>
</figure>
<div class="clearfix"> </div>
If you ever want to make sure you have those extra spaces removed, you can always wrap the entire formula in a trim function, which will remove the extra spaces for you. (See Figure 32.20)
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_32.20.png" alt="BUS115_image_32.20.png" id="img_GwoH" name="img_GwoH">
<figcaption class="text-center">Figure 32.20</figcaption>
</figure>
<div class="clearfix"> </div>
The Zip Code will be the most straightforward step. In this column, we’ll be using the =RIGHT function. Use the following steps to extrapolate the zip code from the City, State, Zip Code column:
<ol id="ol_nmnM" name="ol_nmnM">
<li id="li_LNVp" aria-level="1" name="li_LNVp">Type =RIGHT and a left parenthesis ( ( ) to start the function.</li>
<li id="li_rWrt" aria-level="1" name="li_rWrt">Select the cell containing the City, State, Zip Code data and type a comma ( , ).</li>
<li id="li_AGBU" aria-level="1" name="li_AGBU">We only want the five characters from the right of the cell, so type the number 5. </li>
<li id="li_dbTi" aria-level="1" name="li_dbTi">Embed the =RIGHT formula in a value by typing value( between the equal sign ( = ) and RIGHT.</li>
<li id="li_tpzz" aria-level="1" name="li_tpzz">Add a left parenthesis ( ( ) after value to embed the function. </li>
<li id="li_bDeS" aria-level="1" name="li_bDeS">Close the formula with a right parenthesis ( ) ) at the end. See Figure 32.21 for a completed formula.</li>
</ol>
<figure class="col-12 text-center"><img class="img-fluid" src="https://books.byui.edu/content_images/bus_115_business_app/BUS115_image_32.21.png" alt="BUS115_image_32.21.png" id="img_hTXW" name="img_hTXW">
<figcaption class="text-center">Figure 32.21</figcaption>
</figure>
<div class="clearfix"> </div>
Now that you have the City, State, and Zip Code in separate columns, select all three input cells and click and drag down to automatically update the columns. It will even account for spaces between words, like Little Rock. 
These text functions are invaluable when manipulating data; take the time to learn them to manipulate text quickly and accurately.
<div class="callout default" data-callout-custom-class="default">
<h3 id="h3_CLDo" name="h3_CLDo">Supplemental Resource</h3>
<iframe id="kaltura_player_1636661561" src="https://cdnapisec.kaltura.com/p/1157612/sp/115761200/embedIframeJs/uiconf_id/33020032/partner_id/1157612?iframeembed=true&playerId=kaltura_player_1636661561&entry_id=1_u5jc44hz" width="480" height="270" frameborder="0" allowfullscreen="allowfullscreen"></iframe>
</div>