Skip to main content

How to Split Excel Values into Two or More Columns Using Delimiter

By John Bennet
Most of us are aware of the concatenation utility support provided in MS Excel to combine two cell fields values into one. Whereas how many of us are really aware of splitting the concatenated values from a specific field into two or more fields values.

To achieve this scenario, like programming languages that use a separator called Delimiter to isolate different variables. Excel uses similar delimiter logic to separate the data values of excel cells into two or more values in different consecutive cells.

Read Also: How to Highlight Excel Search word In colors of Choice

Separate Excel Data Values of One Cell to Many Cells
Just follow the below screenshot and explanation to achieve this split cells event in Microsoft Excel.

Split Excel Values Into Several Columns
  1. Step: Select a column containing rows of data that have to be separated and then Go to Data -> Text to Columns.
    Select Values then Data - >Text to Column
  2.  Step: Select Delimited and then click on Next immediately a new wizard (Convert Text to Columns Wizard) prompts like below screenshot for configurations.
    Convert Text to Columns Wizard Step 2
  3.  Pick separators from the existing column values for Eg Mr.James Smith contains (1 dot and 1 Space as a separator and then click on Next to proceed to final configuration settings.
    Convert Text To Columns Wizard Step 2
  4. Now select column data format and Destination columns(For eg $B$2 denotes the split values resultants starting from B column 2 row) and then click on Finish.
    Convert Text To Columns Wizard Step 3
  5. And you're done, the output will list in other columns based on the number of separators used and contains in the fields as per the below screenshot.

    Output/Result

Labels

Labels:

Comments

Post a Comment

Popular posts from this blog

How to Disconnect Bsnl BroadBand and Landline Services Online

By John Bennet
Most of the service providers and executives are experts in facilitating the procedures on how to avail the service but will keep their service termination procedures around the corner which will make it difficult for the users to switch on to other networks immediately and easily. Did you know there are people who rely on their faulty landline/broadband connection simply for the complex procedures and time it would take to close the faulty connection? But here in this blog, we have grabbed the termination process from around the hidden corners of the Bsnl portal to facilitate the Bsnl phone disconnection process in simple steps. Note: You can be an existing BSNL customer for years, however, it is mandatory to have an online account registered in the BSNL customer relationship management(CRM) self-care portal to access the online services. If you don't have one, create one by clicking on the below respective links to access your region Bsnl self-care portal for account mana...
Post a Comment
Read more

How to Turn-Off Auto Correct in WhatsApp & Other Messengers

By John Bennet
Dictionary is not only for the people who don't understand the meaning of a word but also used to know the spelling of a word because there are certain words that may phonetically sound the same but spell differently. Using short forms/abbreviated terms during chats is common among teens of this generation for an easy and fast way to communicate and convey information to anyone in contact. Though most of the short forms are auto-added in Messenger dictionary by default but short forms are extended day by day which interfere while typing to project a sentence but eventually auto-corrected to something else by the android keyboard bots. Also Read: How to Play iPhone(.mac) video format files in Android  How to Turn ON/Off Predictive Text While Typing in Messenger? Did you know! Text auto-correction is not a distinctive feature of Whatsapp, the WhatsApp or in any other social messenger's typewriter will be incorporated with either the Android default keyboard ...
Post a Comment
Read more

How to Register and Activate SBI Internet Banking Online

By John Bennet
Internet Banking is the most trusted and efficient way of making transactions, bill payments, view accounts, view statements, deposits and many more facilities that can be accessed from any corner of the world for free. To avail these facilities in SBI (or) any  State Bank Groups new customer's will be given the PPK kit along with the passbook at the time of creating accounts in the bank itself if the internet banking facility request was mentioned in the Account opening form, but for existing customer's they have to register separately to get access to the internet banking either by visiting a branch or without visiting a branch. Also Read:   10 safety tips for secure banking online - That You Must Know If the main motive of internet banking is to ease transactions and payments for clients in the leisure, without visiting a bank home branch in person, then why not to make the registration and activation process of internet banking also in the leisure from their ...
81 comments
Read more