How to Separate Text or Word or Phrase in Excel

  • Facebook
  • Twitter
  • Delicious
  • StumbleUpon
  • Pinterest
  • Google Plus

First we thank to Microsoft for providing this wonderful software. It is very easy to separate text or word or phrase in a single cell. Excel’s ‘text to columns’ feature allows you to separate the text or word or phrase in a cell. You need one unique letter in the cell to accomplish your requirement. Follow the below given examples for you better understating.

Example1:

Assume that you are having a list of employee’s full name (eg. David Billa, Kathy White) that joined newly into your organization and you have to create an email id for them. Here you have to put last name first and first name last to accomplish mail merge. First step is to separate all first and last names and then make use of it as you required.

Follow the below screen shots on how to separate the first and last names. While doing this make sure that the other columns does contain any values.

Separate Text in Excel

On the above screenshot you can find the names listed on column A, replace all blank space with comma (,). We are doing this to create a common object to separate the first and last name; we can also use space as a common object. Refer the below screenshot for better understanding.

Separate Tex in Excel Replace


Once you have done with replacement, select the column where you want to apply delimited. Then ‘select data’ on main menu and select ‘text to columns’ on sub-menu, you can find this sub-menu at the fourth panel in sub-menu section under ‘data tools’. Now you can see the ‘convert text to columns wizard’ popup on your screen, in that select ‘delimited’ and then press next button. Refer the below screen shots.

Separate Text in Excel Delimited
Delimiters Select

Here at above screenshot you can find the delimiters, select other check box and type , (comma)in the text box parallel to other, then press finish. If there is any existing data in the cells where the separated data will be placed, a dialog box appears asking if you want to replace the content of the destination cells. To replace the data click okay, else click cancel. Copy the values of other columns in some other sheet and continue the process. Refer below screenshot for your clear understanding.

Delimited Comma
Delimited Success

Example2:

http://www.abcd.com/author/david.html
http://mobiles.abcd.com/samsung/model-78.html
http://cars.sample.com/chennai/alllocalities.html
http://www.facebook.com/pages/irfan/32453213234
http://karthik.blogspot.com/how-to-write-test-case

If you want to separate only domain from the above list, delimited is the only option to make the process easy.

Just replace .com/ with .com@, after replacing keep @ as a common object and continue the delimited process.

Like(0)Dislike(0)

Share and Enjoy

prabaharancs@gmail.com'

SurFolks

SurFolks, the one stop destination to cater all your wants and needs.

Leave a Reply