Separate a string of values into different columns (Part III)

Spliting strings of values which are separated by a symbol is a breeze using the text to column function. What happens when you have a string without any separator, like those given below?

tops278484092828

name732180244063

baby276540921867

You can use the fixed width option instead of the delimited option in "Text to Column" function. For those who are using Excel 95 or 97 or evenExcel 2000, you might need to convert the font to one that is fixed width. This means that a "M" will occupy the same width as an "I". The font type I recommend is "Courier" or "Courier New".

Here are the steps:

1) Highlight the rows you want to parse.

2) Change the font to Courier.

3) Goto menu, Data, Text to Column

4) In step 1 of the wizard, select the option "fixed width". Click Next

5) Point the mouse pointer at the appropriate width and click the left mouse button once.

6) If you want to remove any of the preset separator, click the left mouse button at the appropriate black line or separator twice.

7) Once you have set the relevant width, click finish and the strings of value are separated into the respective columns.

Separate a string of values into different columns (Part II)

We found a way to split the values into the respective columns if they are separated by commas. What happens if they are separated not by commas but by the sign \ or ! or ~ ....... Is it possible then to separate them? The answer is yes. If we have a set of values which are separated by "" like those below , we can also split them up into the respective columns.

top 100 baby names\2784840\92828

baby names\732180\24406
baby\27654\09218

This is how it should be done.

1) Highlight the rows you want to break up.

2) On the menu, select data, text to column.

3) In Step 1 of the wizard, make sure the option "delimited" is selected.

4) Instead of checking on the comma delimiters box as demonstrated yesterday, check on the box labelled others and input the sign "\" into the empty box that follows. Once you do that, the values are separated by black lines.

5) Click on finish and you are done.

Separate a string of values into different columns

Imagine you have a Comma Separated Values file (CSV in short). When you open the file, Excel fails to separate the values for you. No matter how many times you re-open the file, it remains as follows:

top 100 baby names,2784840,92828
baby names,732180,24406
baby,276540,9218

Are you going to separate them manually? On the above example, there are only 3 sets of values. What if there are 100 rows, 200 rows or more.

In Lotus 1-2-3, the function to separate the values is called parsing. In Excel, you can do the same through the function called "Text to Column".

1) Highlight the rows you want to break up.
2) On the menu, select data, text to column.
3) In Step 1 of the wizard, make sure the option "delimited" is selected. Click next.
4) Check on the comma delimiters box. Once you do that, the values are separated by black lines.
5) Click on finish and you are done.

The values are now separated into the respective columns.

Calculating Working Days

Excel contains a function that helps you calculate the number of working days between 2 dates. It is called Networkdays. The function assumes a 5 days work week. To use the function, you need to activate an add-in called analysis toolpak. You can do this by going to the menu, tools, add-in and check on the box labelled "Analysis Toolpak". You may require Microsoft Office installation disk to activate the add-in.

The function also allows you to add in holidays such that they are excluded if these holidays fall on weekday and are within the period for the calculation.

Find out the number of unique products in the list (Second formula Explained)

We have covered the first formula last week, what about this second formula
=SUM(IF(FREQUENCY(IF(LEN(A1:A100)>0,MATCH(A1:A100,A1:A100,0),""),IF(LEN(A1:A100)>0,MATCH(A1:A100,A1:A100,0),""))>0,1))?

Before we share with you how the different functions are applied, do take note that the function starts with Sum(if and not sumif. There is a bracket in between the sum and the if. This denotes that it is a multi-conditional sum. To activate the formula, you need to use Ctrl + Shift + Enter together.

What are the conditions in the multi-conditions sum? Let's break them up and see what they meant

Click here for diagram


The formula Len(B1:B8)>0 is used to determine that the cell in the range is not a blank. If there is a character in the cell, then the length is at least 1 and it will return true as shown.

The formula shown in column D is developed to indicate the first row that the content shows up. For example, Internet marketing in row 6 returns a value of 3 because the phrase "internet marketing" shows up the very first time in Row 3.

In Column E, the formula frequency will calculate the number of times the number 1 shows up in the list in column D, followed by 2, 3, ..etc. Notice the simlarity in this formula with the complete formula =SUM(IF(FREQUENCY(IF(LEN(A1:A100)>0,MATCH(A1:A100,A1:A100,0),""),IF(LEN(A1:A100)>0,MATCH(A1:A100,A1:A100,0),""))>0,1)) In fact, the range D1:D8 as shown in the simplified formula is replaced by the formula show in prior column D. The replacement moves us closer to the complete formula. For clarity, the simlarities are highlighted in blue and green colour. Different colours are used to show that the range D1:D8 are replaced twice.

=SUM(IF(FREQUENCY(IF(LEN(A1:A100)>0,MATCH(A1:A100,A1:A100,0),""),IF(LEN(A1:A100)>0,MATCH(A1:A100,A1:A100,0),""))>0,1))

In the final part shown in column F, the formula =if(E5>0,1,0) is used to pass instruction to Excel to put in the number 1 in those rows in column F where the numbers showed up in column E is more than 0. By putting the conditional sum in the formula, we have completed the entire formula where the end result shown is the number of unique records in the list. Do remember to use Ctrl + Shift + Enter together to effect the formula.