Wednesday, June 22, 2011

Excel Tutorial - How To Split Text Or Numbers

Imagine you have a long list of product part numbers. Lets assume they are in this kind of format 100.09.20 which I would say is a common format to write part numbers. Some may even include text, but it makes no difference. 

Now the question comes, 'How do I split this sequence of numbers?' Let's say I want to obtain the last 2 numbers in the sequence. 

Select the data column that you need to separate then go to Data > Text to Columns

Our data is separated by '.' which means that if we could separate the numbers based on '.' you would get the data that you want. For that option, choose the option Delimited and click Next. 
Now you can readily separate data by tab, semicolon, comma or space. Since we are using '.' we will need to click Other and input the character '.' into the accompanying box. You will see the sample of how the data will be split in the preview below it. 
Click Next and you will be brought to the following window. You can change the format of each of the new column. For now lets ignore this and click Finish.
There you have it. The part numbers are split up into editable formats.

Ok, what if the numbers are not so neatly formatted. Something like this perhaps?
In this case, you will need to choose the Fixed Width option like below. 

Clicking Next will bring you to the following window. Just click on where you want the split to be. To have multiple splits, just click in another character column. 
Click Next and you will be brought to the Formatting windows again. Change the column to your required format or just click Finish. I split the numbers twice into the data below.

No comments:

Post a Comment