Here, we will learn how to use the Excel text function: MID. Later, we will also learn how to separate each letter of text using MID with the COLUMN function.
MID function
The Excel MID function extracts the given number of characters from the middle of the given text string. The function will extract based on the given starting location. For example, =MID("becomes",3,4) returns "come". The formula =MID("0123",1,1) returns 0 (zero). The function always returns the result in the text format even if the return value is a numeric value.
MID takes three arguments, all of which are required: text, start_num, num_chars. The first argument text will take the text from which to extract characters. User can enter the value directly in the formula enclosing it with the double quotes (""), or a cell reference containing the value. The second argument start_num will take an integer that specifies the starting location of the first character to extract. The final argument num_chars will also take an integer that specifies the number of characters (starting with start_num) to extract.
Separating each letter of text using MID with COLUMN
To separate each letter of text, use MID and supply the COLUMN function (returns the column number for a reference) in the start_num argument of MID.
The dataset (shown in the image) contains a list of names in column A. The goal is to separate each letter of each name in the corresponding cells. The formula in B2, copied right and then down, is:
=MID($A2,COLUMN()-1,1)
How this formula works
The function is supplied a cell reference A2 that contains the text string "Daniel Anderson". The cell reference A2 contains the dollar sign ($) that is added in front of the column letter A. This dollar sign denotes that the column letter A will remain constant, when the formula is copied to other cells.
The expression =COLUMN()-1 is evaluated. =COLUMN() returns 2 as the formula is in column B and this column is second column in the spreadsheet. 2-1 is evaluated that returns 1. 1 goes to the start_num argument of MID. This tells MID to extract from the first position of the text string "Daniel Anderson" and extract 1 character. Therefore, the formula returns the letter "D". Dragging the fill handle to the right till P2 extracts each character of the text "Daniel Anderson". Then, drag the fill handle to the bottom to complete the process for rest of the names.
You have successfully learnt how to use the Excel text function: MID, and how to separate each letter of text using MID with the COLUMN function! I hope this post helped you.
