ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Using the CONCATENATE, LEFT, RIGHT and MID functions in Excel 2007

Updated on October 6, 2012

Introduction

Hi, and welcome to my latest hub on joining or splitting text using Excel 2007. Names or other text do not always come into your workbooks in exactly the format you want them to, so this hub will introduce you to some of the functions Excel 2007 has built in to help you get your data formatted exactly how you want it to be formatted. You can separate names, addresses or in fact any text you desire in almost any way you can think of. This hub will take you through a number of examples to illustrate what is possible in Excel 2007.

Suppose for example, you have a cell that contains John Smith and you want to split it into two cells, one containing John and the second containing Smith. Using the LEFT and RIGHT functions, you can create a formula in Excel 2007 to accomplish this.

If you have two cells containing a person’s first name in one cell and second name in a second cell and you want to combine them into one cell, you can use the CONCATENATE function to do this.

Splitting text using the Convert Text to Columns Wizard

Before we delve into functions, there is another way to split data in Excel 2007. It uses the Text to Columns button in the Data Tools group on the Data tab.

To begin, select the text you wish to separate and then click on the Text to Columns button

Select Delimited as the file type that best describes your data as below

Text to Columns Wizard Step 1 of 3 in Excel 2007.
Text to Columns Wizard Step 1 of 3 in Excel 2007. | Source

On the next screen, you need to decide which delimiters you wish to use. In my example, my data is separated by spaces, but you can use any character you wish as a delimiter.

If you have multiple spaces (delimiters) in your cells, select Treat consecutive delimiters as one

As you see from the screenshot below, in the Data Preview tab, Excel 2007 shows you how the data will look once you click Finish

Text to Columns Wizard Step 2 of 3 in Excel 2007.
Text to Columns Wizard Step 2 of 3 in Excel 2007. | Source

Unless you are unhappy with the preview, clickFinish

Excel will now have spilt your data into adjacent columns based on your selected delimiter

Splitting text using the LEFT and RIGHT functions

Splitting text can also be accomplished using the LEFT and RIGHT functions in Excel 2007. These are not limited to using delimiters to split text; they can split off a specific number of characters or can search for a specific character and split everything before that character.

Suppose I have a cell containing

Felicity Brown

I want Excel 2007 to take the first five characters in that cell and put them in another cell.

If I use the following formula,

=LEFT(J17,5)

The result of that formula will be

Felic

In my example, I want to use Excel 2007 to give me the first name of the name contained in a cell. The names are separated by spaces as you can see from the picture below. Using the LEFT function in the formula =LEFT(J15,SEARCH(" ",J15)) provides me with the correct result (in this case Bill).

Illustration of how to use the LEFT function to obtain a first name from a cell in Excel 2007.
Illustration of how to use the LEFT function to obtain a first name from a cell in Excel 2007. | Source

To obtain the surname, I use the RIGHT function. This time, I want Excel 2007 to give me everything to the right of the space (in other words the surname). The formula for this is

=RIGHT(J15,LEN(J15)-SEARCH(" ",J15)) as illustrated in the picture below.

Illustration of how to use the RIGHT function to obtain a surname from a cell in Excel 2007
Illustration of how to use the RIGHT function to obtain a surname from a cell in Excel 2007 | Source

Now suppose I had a customer who had a middle initial and I wanted to split them into their own cells, so that I had first name, initial and surname all separated into columns. The LEFT and RIGHT functions in this case would not work, so I use a third function called MID

This formula looks rather frightening, but Excel 2007 is just using the two spaces in the cell to calculate the number of characters between them to extract and then extracting them. The formula is shown below.

Using the MID function to extract a middle initial from a cell containing a name in Excel 2007.
Using the MID function to extract a middle initial from a cell containing a name in Excel 2007. | Source

Within the formula, it is worth pointing out that there are spaces before and after the initial that is extracted from the name which will make life much easier should you want to use the middle initial for another purpose. The final +1 in the formula (bolded below) controls adding a space after the initial.

=MID(J15,SEARCH(" ",J15,1),SEARCH(" ",J15,SEARCH(" ",J15,1)+1)-SEARCH(" ",J15,1)+1)

NOTE: as the formula looking for spaces to separate out the middle initial(s) from the first and last name, should someone have three middle initials, as long as they are not separated using spaces, the formula will still produce the correct result.

Joining together text

My favourite function when working with text is CONCATENATE. In my music store, I like to send emails to customers thanking them for their custom. I would like to send them a reasonably personalized but automatically created email based on their order details in my Excel 2007 workbook. CONCATENATE can help me achieve that.

Below I have my order book

Order Book created in Excel 2007.
Order Book created in Excel 2007. | Source

First I extract the first name from the customer name using =LEFT(Q2,SEARCH(" ",Q2)). I then create my email using CONCATENATE. Using the following formula

Example of a formula using the CONCATENATE function in Excel 2007.
Example of a formula using the CONCATENATE function in Excel 2007. | Source

This gives me the following output:

Hi Bill, Thanks for your recent purchase of Bat out of Hell by Meatloaf

I can then paste that into an email template I have already created and send it off. I can also re-use this formula to automatically create this text for all of my customers in my order book.

Conclusion

Using the functions outlined in this hub, you can easily extract first names, surnames, middle initials or any other text from cells you desire using the LEFT, RIGHT and MID functions in Excel 2007. You can also join cells or text together using the CONCATENATE function. I hope that my examples used in this hub have given you some ideas as to how you can take these functions and use them in your day to day work with Excel 2007. Please feel free to leave any comments you wish in the comments box below, thanks again for reading!

I also have a number of other hubs on aspects of Excel 2007, covering everything from Conditional Formatting to creating charts and graphs. I have an Index hub which also covers how I successfully transitioned from Excel 2003 to 2007 as well as outlining my other Excel 2007 hubs which can be found here

http://robbiecwilson.hubpages.com/hub/How-to-adjust-to-Excel-2007-from-previous-versions-as-well-as-step-by-step-guides-to-many-functions-in-Excel-2007

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)