Class Three ; Functions in Excel (Split function, Join function function, Transpose and Drop down list)

To split Data from one column into two or more columns.
Splitting two or three words in one column into separate columns can be achieved through a process called “column splitting” or “column separation.” This process involves identifying the delimiter or separator used between words and using it to split the text into multiple columns. Here’s a general process to split one column into two or more columns: Identify the Delimiter: Determine the character or pattern that separates the words in the column, such as a comma, space, or a specific character. Example; (James,Mark,Abo-enterprise) Separate the full name to 3 different columns.
Highlight the data, go to text to column tick delimited and click next
and tick comma box, click next then on the destination go and highlight
the first row of the 3 columns that you want to split the name to and then click finish

Note; we tick comma because it is comma that separate the names.

JOIN FUNCTION

The join function is used to combine two or more text strings into a single string. For example, if you had the strings “Moses” and “Ada”, in two separate columns, the join function could combine them into the string “Moses Ada” in one column.

To achieve this, we used Concatenate function

= Concatenate (A4, “ “,B4)

In the above function A4 represent column that contains Moses while B4 represent column that contains Ada.

=A4&” “&B4

TRANSPOSE FUNCTION

The transpose function is used to convert a horizontal set of data into a vertical set of data, or vice versa. For example, if you had a spreadsheet with data organized in 10 columns and 5 rows, the transpose function could convert that data into 5 columns and 10 rows.

First step to transpose is to highlight the data and click control C to copy the data and click an empty cell where you want the transpose data to be and go to past option click the drop down and select paste special then a dialog box will appear scroll down and tick transpose box and click Ok.

DROP DOWN LIST

A drop-down list is a feature in Excel that allows you to select a value from a pre-defined list of options. Drop-down lists are useful for controlling data entry, ensuring consistency, and preventing errors. They can also make it easier for users to enter data, as they don’t have to type values manually.

The first step is to select the cells where you want the drop-down list to appear. Then, you’ll go to the “Data” tab and click the “Data Validation” button. On the “Data Validation” window, you’ll select “List” from the “Allow” drop-down menu. Next, you’ll type the items you want to appear in the drop-down list, separated by commas. Finally, you’ll click “OK”.