<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html><head><title>Text Functions in Excel</title> <meta name="description" content="Text functions in Excel help to analyze text data also perform analysis on the data. Interesting text functions are left, right, mid, Instr, find, search, len, etc. "> <meta name="keywords" content="text functions excel"></head><body bgcolor="#339999"> <table align="center" bgcolor="#ffffff" width="800"> <tbody><tr><td colspan="2" align="center"><h1>How to use text functions in Excel</h1></td></tr> <tr><td colspan="2"><center><script type="text/javascript"><!-- google_ad_client = "pub-1379996146055698"; /* 728x15, created 8/14/08 */ google_ad_slot = "1153145213"; google_ad_width = 728; google_ad_height = 15; //--> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script></center><br> <p>To separate the full name into first name and last name, click on the Data tab and then click on 'Text to Columns'. In the new window that pops up, select 'Delimited' under the heading 'Choose the file type that best describes your data. Then click on the 'Next' button. In the new window select 'comma' or 'space' under the header 'Delimiters' depending on whether the name is separated by comma(s) or space(s).On proper selection you should see the correct preview of the data under the header 'Data preview'. Now click next and finally finish. Apply appropriated headers to your separated data like first name, middle name and last name or surname.<br> <br> <b>=left(B4,find(",",B4)-1)</b><br>You can also use a text function like left() to extract the first name as shown in the formula above. The process involves first finding the delimiter, then subtracting 1 from the result to remove the comma and then using the left function.<br> <b>=len(B4)=12</b><br> <b>=find(",",B4)=6</b><br> <b>=RIGHT(B4,len(b4)-find(",",B4)-1)</b><br>In the case of finding the last name or surname you need to first find the length of the string or text. From this value you minus the value obtained by using the function find. Finally the use of the right() function with these two parameters as shown in the formula above gives you the accurate last name. In case you also have to extract the middle name you'll have to use a macro which we have also published under macros. In this case you can quickly use the text to column function.<br>Watch the training video to get the details. The video is in Hindi. So if you don't understand the language just put off the sound!<br> </p><center> <embed src="http://www.familycomputerclub.com/excel/training/text-functions-excel.wmv" quality="high" bgcolor="#217" align="middle" height="480" width="720"> </center><br> <center><a href="http://www.familycomputerclub.com/excel/training">Text Functions in Excel</a><br> <center><script type="text/javascript"><!-- google_ad_client = "pub-1379996146055698"; /* 728x90, created 3/10/10 */ google_ad_slot = "4763244959"; google_ad_width = 728; google_ad_height = 90; //--> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script></center> </center></td></tr></tbody></table> </body></html>