Convert Text to Columns

With Excel there is always more than one way of completing a task.  Previously I have written a post about splitting date and time from being combined in one cell.  This was based upon using Excel Functions, LEFT, MID, RIGHT and then concatenating the result together,  But, there is another way to do the same job without having to know Excel Functions.  You can simply use the Text to Column feature that is standard in Excel!

text-to-columnIn the image to the left you will see column A contains date and time combined together in each cell.  Over the next few steps we will seperate the date and time into seperate columns.  Column A will contain date and column B will contain the time.  It is important to note that you ensure you have a spare blank column or two to your right, so you can fill it with the newly seperated data.

Selecting the Text to Columns Wizard from the Menu Bar

So first you need to highlight the cells that you want to convert to columns.  There are two ways to do this, either select the entire column by clicking on the A button at the top of column.  Or click in cell A1 and then with the mouse button still pressed, drag the mouse down to the last cell with data, in this case it’s cell A21.  Now the cells are selected go to the menu bar at the top of screen and select Data > Text to Columns.  Once you have clicked Text to Columns a pop up box will appear, this is the Convert Text to Columns Wizard (as shown in the image below).text-to-column-1

The Three Steps to Using Text to Columns Wizard

convert-text-to-column-wizardStep One
Using the Excel Text to Column Wizard is very easy.  Step one, ensure you have the tick box called Delimited ticked.  It is important to have this one selected rather than Fixed Width as the delimited option is how you define seperating the text.  The options include, commas, tabs, slashes, in fact almost any character.  Once Delimited is selected click the Next button to progress to step 2.

Step Two
convert-text-to-column-wizard-step-2The second step is to select how Excel will seperate the text.  As mentioned in the previous step, this can be almost any character.  But for this example we want to split the data where the space is between the date and the time.  So tick the Space box on the form.  Once you have clicked it you will see in the preview window that Excel shows you how the text will look split into columns.  Be sure that the date is one column and the time in another.  If you are happy that is seperated correctly then click Next.

Step Three
convert-text-to-column-wizard-step-3Last step in the wizard.  If everything looks fine here, then simply click finish.  But you can select where the text will be enter.  On the right hand side of the form is a tick box called Destination this allows you to manually pick a cell to start from.  If you wish to do this simply click the small spreadsheet logo to the right of the box, the form will minimise and you can now pick a cell.  Click the cell you want the data in, and then click the small spreadsheet logo again which should have an arrow pointing upwards.  Finally click Finish and you should now have the date and time seperated into columns.

If after splitting the text to columns and the date still shows a time value, then highlight those cells, right click and select format cells.  Then select Date.  This will then format those cells as Date.

4,047 thoughts on “Convert Text to Columns

  1. w88

    Right here is the right webpage for anybody who would like to find out about this topic.

    You know so much its almost hard to argue with
    you (not that I actually would want to…HaHa).
    You definitely put a brand new spin on a subject that’s been discussed for years.
    Wonderful stuff, just great!

  2. poboleda

    Excellent web site you’ve got here.. It’s hard to find good quality writing like yours nowadays. I really appreciate individuals like you! Take care!!

  3. udfauzi.com

    Everything is very open with a clear description of the challenges. It was truly informative. Your site is very useful. Many thanks for sharing!

  4. http://143.95.144.116/

    A fascinating discussion is definitely worth comment. I believe that you need to write more on this topic, it might not be a taboo subject but typically people don’t speak about these subjects. To the next! Kind regards!!

  5. vivo slot

    This is a good tip particularly to those fresh to the blogosphere. Simple but very accurate information… Thanks for sharing this one. A must read article!

  6. vivoslot.online

    I blog often and I truly appreciate your information. The article has really peaked my interest. I will bookmark your site and keep checking for new details about once per week. I subscribed to your Feed too.

  7. daftar vivo slot

    I’m excited to uncover this website. I want to to thank you for ones time for this wonderful read!! I definitely appreciated every little bit of it and I have you saved to fav to check out new information in your blog.

  8. sponduu indonesia

    Jasa Backlink dengan backlink PBN dan SEO dapat meringankan menaikan rangking web kamu
    dgn cepat
    seandainya kamu pusing merangking website apabila kamu
    binggung menaikan merek business kamu atau jika anda galau dan sudah selesai keluar biaya yg tidak sedikit terhadap optimasi web kamu maka dikala ini,
    kamu berada di web yang serasi abdi berharap biar kita berjodoh dan mendapatkan rejeki untuk berjerih payah dengan sebab rejeki itu berasal bersumber suatu
    pertalian yang baik tanpa memotivasi membebankan satu bersama yang lainnya.

    Sponduu Indonesia premium digital marketing partner Anda
    Jasa backlink SEO bermutu dgn PBN (Private Blog Network) pada
    optimasi situs bersama aman, permanen dan pengasuh pelayanan jasa iklan google adwords yang di melaksanakan oleh Sponduu Indonesia untuk menmbahkan suksesi atau optimalkan branding maskapai anda di search engine, bersama pengalaman yang sudah matang dan tidak sedikit melakukan belajar keluhan dalam mencari struktur yang baik dan benar buat tiap-tiap pemakai telah mendatangkan abdi juga sebagai pengganti yg tepat terhadap
    kamu Tentu sudah ratusan klien yg diterima kami optimasi dan puluhan pelanggan baru setiap bulan nya, mereka memilih
    aku bagi menolong dan menaikan seri situs mereka di search engine tidak dengan repot.

  9. download apk vivoslot

    Having read this I thought it was rather informative. I appreciate you finding the time and effort to put this information together. I once again find myself spending way too much time both reading and commenting. But so what, it was still worth it!

  10. vivoslot apk

    Spot on with this write-up, I seriously think this amazing site needs a lot more attention. I’ll probably be back again to read through more, thanks for the information!

  11. 188bet

    I appreciate, result in I found exactly what I used to be looking for.

    You’ve ended my 4 day lengthy hunt! God Bless you
    man. Have a nice day. Bye

  12. kanjivaram saree

    That is a great tip especially to those new to the blogosphere.
    Brief bᥙt very precise information� Thank you foг sharing this one.
    A must гead post!

  13. vivoslot

    I’m impressed, I must say. Seldom do I come across a blog that’s both equally educative and engaging, and let me tell you, you have hit the nail on the head. The issue is something which too few folks are speaking intelligently about. I am very happy that I found this during my search for something relating to this.

  14. dingdong togel

    I’m impressed, I must say. Seldom do I encounter a blog that’s both educative and amusing, and let me tell you, you have hit the nail on the head. The problem is an issue that not enough men and women are speaking intelligently about. Now i’m very happy that I stumbled across this in my search for something relating to this.

  15. case strategies group

    Hey I am so happy I found your web site, I really found you by mistake, while
    I was browsing on Bing for something else, Anyhow I
    am here now and would just like to say kudos for a remarkable post and a all round enjoyable blog (I also love
    the theme/design), I don’t have time to go through it all at the minute but I have
    book-marked it and also added in your RSS feeds, so when I have time I will be back to read much more, Please do keep up the awesome
    work.

  16. dingdong 24d

    An intriguing discussion is worth comment. I do think that you need to write more on this issue, it might not be a taboo subject but generally people don’t discuss such issues. To the next! All the best!!

  17. agen judi online

    Having read this I believed it was rather enlightening. I appreciate you spending some time and energy to put this information together. I once again find myself spending a lot of time both reading and commenting. But so what, it was still worth it!

  18. dominoqq

    That is a good tip particularly to those fresh to the blogosphere. Simple but very accurate information… Thank you for sharing this one. A must read article!

  19. agen judi online terpercaya

    An impressive share! I have just forwarded this onto a friend who has been conducting a little research on this. And he in fact ordered me dinner due to the fact that I stumbled upon it for him… lol. So let me reword this…. Thank YOU for the meal!! But yeah, thanx for spending some time to talk about this issue here on your blog.

  20. dominoqq

    It’s nearly impossible to find experienced people for this subject, but you seem like you know what you’re talking about! Thanks

  21. Pylon radiation

    My developer is trying to persuade me to move to .net from PHP.

    I have always disliked the idea because of the expenses.
    But he’s tryiong none the less. I’ve been using WordPress on several websites for about a year and am
    worried about switching to another platform.
    I have heard great things about blogengine.net. Is there a way I can import all my wordpress content into it?
    Any help would be greatly appreciated!

  22. togel hk

    I’m impressed, I have to admit. Seldom do I come across a blog that’s equally educative and engaging, and without a doubt, you’ve hit the nail on the head. The problem is something that too few people are speaking intelligently about. Now i’m very happy that I stumbled across this in my search for something concerning this.

  23. dominoqq

    Everything is very open with a clear explanation of the issues. It was truly informative. Your site is extremely helpful. Thank you for sharing!

Comments are closed.