{"id":12201,"date":"2014-06-06T16:08:09","date_gmt":"2014-06-06T16:08:09","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/wp\/?p=3807"},"modified":"2014-06-06T16:08:09","modified_gmt":"2014-06-06T16:08:09","slug":"text-to-columns-function","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2014\/06\/text-to-columns-function\/","title":{"rendered":"Spreading data across Columns in Excel Without VBA Code"},"content":{"rendered":"

In my previous article<\/a> as you learnt how to split data using VBA SPLIT() function<\/strong>. In this Article I am going to explain you how to separate the data across columns of a cell without excel VBA. Microsoft Excel has a built-in function called Text-to-Columns<\/strong> under Data ribbon (refer the Pic 1). Using this option user can split the data of a cell across columns which are joined by a specific delimiter or fixed length. <\/p>\n

How to use Text to Columns Built-in Function of Excel ? <\/h3>\n

Text to Columns function is easy to use. I am going to make you expert in Text to Column function in 5 simple steps.<\/p>\n

5 Steps to spread the data of a cell across columns<\/h2>\n

Step 1. Select Cell<\/h2>\n

Select the cell where your data is there to be split across columns<\/p>\n

Step 2. Click on Text to Columns Option<\/h2>\n

Click on Text to Columns Option available under Data Tab as shown in the below picture
\n 
\n\"Text-To-Columns\"<\/p>\n

Step 3. Choose the Type<\/h2>\n

In this dialog box user can choose the type of data which he\/she wants to split. As you can see in the below picture there are two options available.
\n 
\n1. Delimiter : <\/strong>Choose this option if your data is joined by a delimiter by which you want to split it.
\n1. Fixed length : <\/strong>Choose this option if your data does not have a delimiter rather you want to split it by a fixed length. This option will enable user to drag the split bar to adjust it where user wants.
\n 
\n\"Step
\nFrom the above screen i have selected first option as delimiter<\/strong> as my data is delimited by comma (,) delimiter.<\/p>\n

Step 4. Choose or enter the delimiter<\/h2>\n

In this wizard user is allowed to select one of the already listed delimiter or you can enter your own delimiter by selecting the Other<\/strong> checkbox as shown in the below picture:
\n 
\n\"Step
\nYou can see your data preview how it is split the data<\/i> as shown in the above picture.
\nNote: <\/strong>
\n1. <\/strong> If you had selected fixed width<\/strong> option in above Step 3<\/strong> then you will be allowed to drag the column separator to adjust the width of all the columns.
\n2. <\/strong> In the above wizard you can see one option as Treat consecutive delimiters as one<\/strong>. This option overcomes the problem which occurs in VBA SPLIT() function. In case of consecutive delimiters, SPLIT() function stores an Empty<\/i> character as a split item. To know more about this you can refer this article.<\/a> But here in Text to Columns<\/strong> options you can get rid of this problem by checking this checkbox.<\/p>\n

Step 5. Last Step : Select the destination<\/h2>\n

By default destination is selected as the same cell where the data is to be split. This you can choose your own. If you keep the same cell as destination then your original joint data with delimiters will be replaced by the first split. Here I have selected the destination cell as the adjacent column cell in same row. Hence the first split will be put in the destination cell selected and rest all split will be put in to the separate columns as shown in the below picture
\n 
\nNote: <\/strong> Here in this wizard you can choose the format of the column by selecting the particular column from the Data preview as shown in the below picture:
\n 
\n\"Step<\/p>\n

That’s all. Now you are done. Click on Finish and your data of Cell D4 is spread across columns starting from my selected destination cell D5. Refer the below picture:<\/p>\n

\"Text-To-Columns-Split\"<\/p>\n

\nYour data is spread across columns as you expected without any VBA code. Isn’t this interesting and very useful function of Microsoft Excel? Do you agree with me? Let me know by commenting here !!\n<\/p><\/blockquote>\n

Enjoy your Weekend \ud83d\ude42 <\/h3>\n

<\/p>\n<\/span>","protected":false},"excerpt":{"rendered":"

In my previous article as you learnt how to split data using VBA SPLIT() function. In this Article I am going to explain you how to separate the data across columns of a cell without excel VBA. Microsoft Excel has a built-in function called Text-to-Columns under Data ribbon (refer the Pic 1). Using this option […]<\/p>\n","protected":false},"author":45,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[1672,1673,1676,1682],"tags":[],"class_list":["post-12201","post","type-post","status-publish","format-standard","hentry","category-excel-formula","category-excel-functions","category-excel-tips","category-popular-articles"],"yoast_head":"\nSpreading data across Columns in Excel Without VBA Code - Let's excel in Excel<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/vmlogger.com\/excel\/2014\/06\/text-to-columns-function\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Spreading data across Columns in Excel Without VBA Code\" \/>\n<meta property=\"og:description\" content=\"In my previous article as you learnt how to split data using VBA SPLIT() function. In this Article I am going to explain you how to separate the data across columns of a cell without excel VBA. Microsoft Excel has a built-in function called Text-to-Columns under Data ribbon (refer the Pic 1). Using this option […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vmlogger.com\/excel\/2014\/06\/text-to-columns-function\/\" \/>\n<meta property=\"og:site_name\" content=\"Let's excel in Excel\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:author\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:published_time\" content=\"2014-06-06T16:08:09+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/learnexcelmacro.com\/wp\/wp-content\/uploads\/sites\/11\/2014\/06\/Text-To-Columns.png\" \/>\n<meta name=\"author\" content=\"Vishwamitra Mishra\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/www.twitter.com\/learnexcelmacro\" \/>\n<meta name=\"twitter:site\" content=\"@learnexcelmacro\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Vishwamitra Mishra\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/06\/text-to-columns-function\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/06\/text-to-columns-function\/\"},\"author\":{\"name\":\"Vishwamitra Mishra\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"headline\":\"Spreading data across Columns in Excel Without VBA Code\",\"datePublished\":\"2014-06-06T16:08:09+00:00\",\"dateModified\":\"2014-06-06T16:08:09+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/06\/text-to-columns-function\/\"},\"wordCount\":628,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"image\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/06\/text-to-columns-function\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2014\/06\/Text-To-Columns.png\",\"articleSection\":[\"Excel Formula\",\"Excel Functions\",\"Excel Tips\",\"Popular Articles\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2014\/06\/text-to-columns-function\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/06\/text-to-columns-function\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/2014\/06\/text-to-columns-function\/\",\"name\":\"Spreading data across Columns in Excel Without VBA Code - Let's excel in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/06\/text-to-columns-function\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/06\/text-to-columns-function\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2014\/06\/Text-To-Columns.png\",\"datePublished\":\"2014-06-06T16:08:09+00:00\",\"dateModified\":\"2014-06-06T16:08:09+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/06\/text-to-columns-function\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2014\/06\/text-to-columns-function\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/06\/text-to-columns-function\/#primaryimage\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2014\/06\/Text-To-Columns.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2014\/06\/Text-To-Columns.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2014\/06\/text-to-columns-function\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/vmlogger.com\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel Formula\",\"item\":\"https:\/\/vmlogger.com\/excel\/excel-formula\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Spreading data across Columns in Excel Without VBA Code\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\",\"url\":\"https:\/\/vmlogger.com\/excel\/\",\"name\":\"Let's excel in Excel\",\"description\":\"Let's share knowledge\",\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/vmlogger.com\/excel\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\",\"name\":\"Vishwamitra Mishra\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"width\":528,\"height\":560,\"caption\":\"Vishwamitra Mishra\"},\"logo\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\"},\"description\":\"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.\",\"sameAs\":[\"http:\/\/www.learnexcelmacro.com\",\"http:\/\/www.facebook.com\/vmlogger\",\"https:\/\/x.com\/https:\/\/www.twitter.com\/learnexcelmacro\",\"https:\/\/www.youtube.com\/c\/VMLogger\"],\"url\":\"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Spreading data across Columns in Excel Without VBA Code - Let's excel in Excel","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/vmlogger.com\/excel\/2014\/06\/text-to-columns-function\/","og_locale":"en_US","og_type":"article","og_title":"Spreading data across Columns in Excel Without VBA Code","og_description":"In my previous article as you learnt how to split data using VBA SPLIT() function. In this Article I am going to explain you how to separate the data across columns of a cell without excel VBA. Microsoft Excel has a built-in function called Text-to-Columns under Data ribbon (refer the Pic 1). Using this option […]","og_url":"https:\/\/vmlogger.com\/excel\/2014\/06\/text-to-columns-function\/","og_site_name":"Let's excel in Excel","article_publisher":"http:\/\/www.facebook.com\/vmlogger","article_author":"http:\/\/www.facebook.com\/vmlogger","article_published_time":"2014-06-06T16:08:09+00:00","og_image":[{"url":"http:\/\/learnexcelmacro.com\/wp\/wp-content\/uploads\/sites\/11\/2014\/06\/Text-To-Columns.png"}],"author":"Vishwamitra Mishra","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/www.twitter.com\/learnexcelmacro","twitter_site":"@learnexcelmacro","twitter_misc":{"Written by":"Vishwamitra Mishra","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vmlogger.com\/excel\/2014\/06\/text-to-columns-function\/#article","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/2014\/06\/text-to-columns-function\/"},"author":{"name":"Vishwamitra Mishra","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"headline":"Spreading data across Columns in Excel Without VBA Code","datePublished":"2014-06-06T16:08:09+00:00","dateModified":"2014-06-06T16:08:09+00:00","mainEntityOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2014\/06\/text-to-columns-function\/"},"wordCount":628,"commentCount":0,"publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"image":{"@id":"https:\/\/vmlogger.com\/excel\/2014\/06\/text-to-columns-function\/#primaryimage"},"thumbnailUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2014\/06\/Text-To-Columns.png","articleSection":["Excel Formula","Excel Functions","Excel Tips","Popular Articles"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/vmlogger.com\/excel\/2014\/06\/text-to-columns-function\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/vmlogger.com\/excel\/2014\/06\/text-to-columns-function\/","url":"https:\/\/vmlogger.com\/excel\/2014\/06\/text-to-columns-function\/","name":"Spreading data across Columns in Excel Without VBA Code - Let's excel in Excel","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2014\/06\/text-to-columns-function\/#primaryimage"},"image":{"@id":"https:\/\/vmlogger.com\/excel\/2014\/06\/text-to-columns-function\/#primaryimage"},"thumbnailUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2014\/06\/Text-To-Columns.png","datePublished":"2014-06-06T16:08:09+00:00","dateModified":"2014-06-06T16:08:09+00:00","breadcrumb":{"@id":"https:\/\/vmlogger.com\/excel\/2014\/06\/text-to-columns-function\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vmlogger.com\/excel\/2014\/06\/text-to-columns-function\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/2014\/06\/text-to-columns-function\/#primaryimage","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2014\/06\/Text-To-Columns.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2014\/06\/Text-To-Columns.png"},{"@type":"BreadcrumbList","@id":"https:\/\/vmlogger.com\/excel\/2014\/06\/text-to-columns-function\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vmlogger.com\/excel\/"},{"@type":"ListItem","position":2,"name":"Excel Formula","item":"https:\/\/vmlogger.com\/excel\/excel-formula\/"},{"@type":"ListItem","position":3,"name":"Spreading data across Columns in Excel Without VBA Code"}]},{"@type":"WebSite","@id":"https:\/\/vmlogger.com\/excel\/#website","url":"https:\/\/vmlogger.com\/excel\/","name":"Let's excel in Excel","description":"Let's share knowledge","publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/vmlogger.com\/excel\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5","name":"Vishwamitra Mishra","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","width":528,"height":560,"caption":"Vishwamitra Mishra"},"logo":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/"},"description":"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.","sameAs":["http:\/\/www.learnexcelmacro.com","http:\/\/www.facebook.com\/vmlogger","https:\/\/x.com\/https:\/\/www.twitter.com\/learnexcelmacro","https:\/\/www.youtube.com\/c\/VMLogger"],"url":"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/"}]}},"_links":{"self":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/12201"}],"collection":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/users\/45"}],"replies":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/comments?post=12201"}],"version-history":[{"count":0,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/12201\/revisions"}],"wp:attachment":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media?parent=12201"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/categories?post=12201"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/tags?post=12201"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}