√√ LEARN HOW TO USE ALPHANUMERIC SORTING IN EXCEL

A// ALPHANUMERIC SORTING (WITHOUT SPACING) USING THE MID FUNCTION IN EXCEL APPLICATION; MY DISCOVERY

Alphanumeric data is a combination of alphabets and numbers that usually defines a person’s identity to an organization, institution, company, agency, association and groups. It is commonly used for Identity Cards. In Public elections, it is used for Voter Identity Cards, and demarcation of regions, districts, constituencies, electoral areas and polling stations. It is a unique form of coding that gives meaning to identity of persons and filing of documents in both the public and public sector, governments, political parties, banks, security, education, health sectors, etc.

Here are simple ways to follow with proven results for your study and learning:

MID Function in excel is a text function used to find out strings and return them from any middle part of the Excel. The formula extracts the result from the text or the string itself, the start number or position, and the string’s end position. It takes three arguments as specified below.

=MID(text, start number, number chart) to sort the data. Note that when the data is the same in terms of arrangement and number of alphanumeric, you can scroll below and get the same results for the other alphanumeric data. But if the arrangement and number of alphanumeric data are different, then you need to apply the formula separately and individually. It means that you have to scan and examine each set of alphanumeric data before you apply the mid function formula in an adjustable manner.

 Alphanumeric data: AA26NanaKwakuDuodu is in column A10 and you are to extract into five (5) columns

Extracting the Code: AA

1. Type an equal sign
2. Type MID after the equal sign
3. Begin with bracket sign and then the mid function shows text, start number and number chart

(a) Select the start number which is the same as the Column number. Note that the cell in which the alphanumeric data lies determines the column number to select. For instance in this example, the start number is A10; the same as Column A10. This highlights the A10 cell with the alphanumeric data.

(b) Add a comma and type 1 as the start number

(c) Add a comma and type 2 as the chart number meaning that you want the two alphabets to be extracted.

(d) Close bracket and use the Enter key to make the extraction complete

(e) Scroll down to extract other alphabets in rows below if only they are of the same arrangement and number of letters or do it separately for different arrangements and letters.

This is the formula: =MID(A10,1,2) to get your code AA

Extracting the Number: 26

1. Type an equal sign
2. Type MID function after the equal sign
3. Begin with bracket sign and then the mid function shows text, start number and number chart.

(a) Once the start number which is the same as the Column number is A10, type A10 to highlight the cell meaning that you are ready to extract a portion of data from the cell.

(b) Add a comma and type 3 as the start number; third (3^rd) number counted after the AA code

(c) Add a comma and type 2 as the chart number meaning that you want the two digit numbers to be extracted.

(d) Close bracket and use the Enter key to make the extraction complete

(e) Scroll down to extract other numbers in rows below if only they are of the same arrangement and numbers or do it separately for different arrangements and numbers.

    This is the formula: =MID(A10,3,2) to get your extracted number: 26

Extracting the first name: Nana

1. Type an equal sign
2. Type MID function after the equal sign
3. Begin with bracket sign and then the mid formula shows text, start number and number chart.

(a) Once the start number which is the same as the Column number is A10, type A10 to highlight the cell meaning that you are ready to extract a portion of data from the cell.

(b) Add a comma and type 5 as the start number; fifth (5^th) number counted from the beginning

(c) Add a comma and type 4 as the chart number because the name is counted four (4) letters

(d) Close bracket and use the Enter key to make the extraction complete

(e) Scroll down to extract other names in rows below if only they are of the same arrangement and number of letters or do it separately for different arrangements and letters.

     This is the formula: =MID(A10,5,4) to get your extracted first name: Nana

Extracting the middle name: Kwaku

1. Type an equal sign
2. Type MID function after the equal sign
3. Begin with bracket sign and then the mid formula shows text, start number and number chart

(a) Once the start number which is the same as the Column number is A10, type A10 to highlight the cell meaning that you are ready to extract a portion of data from the cell.

(b) Add a comma and type 9 as the start number; ninth (9^th) number counted from the beginning

(c) Add a comma and type 5 as the chart number because the name is counted five (5) letters

(d) Close bracket and use the Enter key to make the extraction complete

(e) Scroll down to extract other names in rows below if only they are of the same arrangement and number of letters or do it separately for different arrangements and letters.

    This is the formula: =MID(A10,9,5) to get your extracted middle name: Kwaku

Extracting the surname: Duodu

1. Type an equal sign
2. Type MID function after the equal sign
3. Begin with bracket sign and then the mid formula shows text, start number and number chart.

(a) Once the start number which is the same as the Column number is A10, type A10 to highlight the cell meaning that you are ready to extract a portion of data from the cell.

(b) Add a comma and type 14 as the start number; fourteenth (14^th) number counted from the beginning

(c) Add a comma and type 5 as the chart number because the name is counted five (5) letters

(d) Close bracket and use the Enter key to make the extraction complete

(e) Scroll down to extract other names in rows below if only they are of the same arrangement and number of letters or do it separately for different arrangements and letters

   This is the formula: =MID(A10,14,5) to get your extracted surname: Duodu

Note that the chart number for the surname would still be extracted even with any input number beyond 5.

B// ALPHANUMERIC SORTING (WITH SPACING) USING THE MID FUNCTION IN EXCEL APPLICATION; MY DISCOVERY

MID Function in excel is a text function used to find out strings and return them from any middle part of the Excel. The formula extracts the result from the text or the string itself, the start number or position, and the string’s end position. It takes three arguments as specified below.

=MID(text, start number, number chart) to sort the

 Alphanumeric data: AA 26 Nana Kwaku Duodu is in column A18 and you are to extract into five (5) columns.

Note: Spaces in alphanumeric data are also counted as numbers

Extracting the Code: AA

1. Type an equal sign

2. Type MID after the equal sign

3. Begin with bracket sign and then the mid function shows text, start number and number chart

(a) Select the start number which is the same as the Column number. Note that the cell in which the alphanumeric data lies determines the column number to select. For instance in this example, the start number is A18; the same as Column A18. This highlights the A18 cell with the alphanumeric data.

(b) Add a comma and type 1 as the start number

(c) Add a comma and type 2 as the chart number meaning that you want the two alphabets to be extracted.

(d) Close bracket and use the Enter key to make extraction complete

(e) Scroll down to extract other alphabets in rows below if only they are of the same arrangement and number of letters or do it separately for different arrangements and letters.     

This is the formula: =MID(A18,1,2) to get your code AA

Extracting the Number: 26

1. Type an equal sign

2. Type MID function after the equal sign

3. Begin with bracket sign and then the mid function shows text, start number and number chart

(a) Once the start number which is the same as the Column number is A18, type A18 to highlight the cell meaning that you are ready to extract a portion of data from the cell.

(b) Add a comma and type 4 as the start number; fourth (4^th) number counted after the AA code

(c) Add a comma and type 2 as the chart number meaning that you want the two digit numbers to be extracted.

(d) Close bracket and use the Enter key to make extraction complete

(e) Scroll down to extract other names in rows below if only they are of the same arrangement and number of letters or do it separately for different arrangements and letters.

    This is the formula =MID(A18,4,2) to get your extracted number: 26

Extracting the first name: Nana

1. Type an equal sign

2. Type MID function after the equal sign

3. Begin with bracket sign and then the mid formula shows text, start number and number chart

(a) Once the start number which is the same as the Column number is A18, type A18 to highlight the cell meaning that you are ready to extract a portion of data from the cell.

(b) Add a comma and type 7 as the start number; seventh (7^th) number counted from the beginning

(c) Add a comma and type 4 as the chart number because the name is counted four (4) letters

(d) Close bracket and use the Enter key to make extraction complete

(e) Scroll down to extract other names in rows below if only they are of the same arrangement and number of letters or do it separately for different arrangements and letters.

     This is the formula: =MID(A18,7,4) to get your extracted first name: Nana

Extracting the middle name: Kwaku

1. Type an equal sign

2. Type MID function after the equal sign

3. Begin with bracket sign and then the mid formula shows text, start number and number chart

(a) Once the start number which is the same as the Column number is A18, type A18 to highlight the cell meaning that you are ready to extract a portion of data from the cell.

(b) Add a comma and type 12 as the start number; twelfth (12^th) number counted from the beginning

(c) Add a comma and type 5 as the chart number because the name is counted five (5) letters

(d) Close bracket and use the Enter key to make extraction complete

(e) Scroll down to extract other names in rows below if only they are of the same arrangement and number of letters or do it separately for different arrangements and letters. 

     This is the formula: =MID(A18,12,5) to get your extracted middle name: Kwaku

Extracting the Surname: Duodu

1. Type an equal sign

2. Type MID function after the equal sign

3. Begin with bracket sign and then the mid formula shows text, start number and number chart

(a) Once the start number which is the same as the Column number is A18, type A18 to highlight the cell meaning that you are ready to extract a portion of data from the cell.

(b) Add a comma and type 18 as the start number; eighteenth (18^th) number counted from the beginning

(c) Add a comma and type 5 as the chart number because the name is counted five (5) letters

(d) Close bracket and use the Enter key to make extraction complete

(e) Scroll down to extract other names in rows below if only they are of the same arrangement and number of letters or do it separately for different arrangements and letters.

   This is the formula: =MID(A18,18,5) to get your extracted surname name: Duodu

Note that the chart number for the surname (last) would still be extracted even with any input number beyond 5.

C// WHEN ALPHANUMERIC ENDS WITH A SPACED NUMBER

MID Function in excel is a text function used to find out strings and return them from any middle part of the Excel. The formula extracts the result from the text or the string itself, the start number or position, and the string’s end position. It takes three arguments as specified below.

=MID(text, start number, number chart) to sort the

 Alphanumeric data: AANanaKwakuDuodu 26 is in column A3 and you are to extract into five (5) columns.

Note that spaced numbers are counted. Also the space before number 26 is also counted to make it three (3) instead of two (2) for the chart number after the start number.

Extracting the Code: AA

1. Type an equal sign
2. Type MID after the equal sign
3. Begin with bracket sign and then the mid function shows text, start number and number chart

(a) Select the start number which is the same as the Column number. Note that the cell in which the alphanumeric data lies determines the column number to select. For instance in this example, the start number is A3; the same as Column A3. This highlights the A3 cell with the alphanumeric data.

(b) Add a comma and type 1 as the start number

(c) Add a comma and type 2 as the chart number meaning that you want the two digit numbers to be extracted.

(d) Close bracket and use the Enter key to make extraction complete

(e) Scroll down to extract other names in rows below if only they are of the same arrangement and number of letters or do it separately for different arrangements and letters.

This is the formula: =MID(A3,1,2) to get your code AA

Extracting the first name: Nana

1.Type an equal sign

2. Type MID function after the equal sign

3. Begin with bracket sign and then the mid formula shows text, start number and number chart

(a) Once the start number which is the same as the Column number is A3, type A3 to highlight the cell meaning that you are ready to extract a portion of data from the cell.

(b) Add a comma and type 3 as the start number; third (3^rd) number counted from the beginning

(c) Add a comma and type 4 as the chart number because the name is counted four (4) letters

(d) Close bracket and use the Enter key to make extraction complete

(e) Scroll down to extract other names in rows below if only they are of the same arrangement and number of letters or do it separately for different arrangements and letters.

     This is the formula: =MID(A3,3,4) to get your extracted first name: Nana

Extracting the middle name: Kwaku

1.Type an equal sign

2. Type MID function after the equal sign

3. Begin with bracket sign and then the mid formula shows text, start number and number chart

(a) Once the start number which is the same as the Column number is A3, type A3 to highlight the cell meaning that you are ready to extract a portion of data from the cell.

(b) Add a comma and type 7 as the start number; seventh (7^th) number counted from the beginning

(c) Add a comma and type 5 as the chart number because the name is counted five (5) letters

(d) Close bracket and use the Enter key to make extraction complete

(e) Scroll down to extract other names in rows below if only they are of the same arrangement and number of letters or do it separately for different arrangements and letters.

     This is the formula: =MID(A3,7,5) to get your extracted first name: Kwaku

Extracting the surname: Duodu

1.Type an equal sign

2. Type MID function after the equal sign

3. Begin with bracket sign and then the mid formula shows text, start number and number chart

(a) Once the start number which is the same as the Column number is A3, type A3 to highlight the cell meaning that you are ready to extract a portion of data from the cell.

(b) Add a comma and type 12 as the start number; seventh (12^th) number counted from the beginning

(c) Add a comma and type 5 as the chart number because the name is counted five (5) letters

(d) Close bracket and use the Enter key to make extraction complete

(e) Scroll down to extract other names in rows below if only they are of the same arrangement and number of letters or do it separately for different arrangements and letters.

     This is the formula: =MID(A3,12,5) to get your extracted first name: Duodu

Extracting the spaced number: 26

Note that spaced numbers are counted. Also the space before the end number 26 is also counted to make it three (3) instead of two (2) for the chart number after the start number.

1.Type an equal sign

2. Type MID function after the equal sign

3. Begin with bracket sign and then the mid formula shows text, start number and number chart

(a) Once the start number which is the same as the Column number is A3, type A3 to highlight the cell meaning that you are ready to extract a portion of data from the cell.

(b) Add a comma and type 18 as the start number; eighteenth (18^th) number counted from the beginning

(c) Add a comma and type 3 as the chart number because the number is counted three (3) letters including the space.

(d) Close bracket and use the Enter key to make extraction complete

(e) Scroll down to extract other numbers in rows below if only they are of the same arrangement and numbers or do it separately for different arrangements and numbers.

     This is the formula: =MID(A3,18,3) to get your extracted first name: 26

D// SORTING ONLY NAMES (NON – NUMERIC) USING THE MID FUNCTION

Extract the name: Nana Kwaku Duodu into three (3) columns

Extracting the first name: Nana

1.Type an equal sign

2. Type MID function after the equal sign

3. Begin with bracket sign and then the mid formula shows text, start number and number chart

(a) Once the start number which is the same as the Column number is A23, type A23 to highlight the cell meaning that you are ready to extract a portion of data from the cell.

(b) Add a comma and type 1 as the start number; from the beginning

(c) Add a comma and type 4 as the chart number because the name is counted four (4) letters

(d) Close bracket and use the Enter key to make extraction complete

(e) Scroll down to extract other names in rows below if only they are of the same arrangement and number of letters or do it separately for different arrangements and letters.

     This is the formula: =MID(A23,1,4) to get your extracted first name: Nana

Extracting the middle name: Kwaku

1.Type an equal sign

2. Type MID function after the equal sign

3. Begin with bracket sign and then the mid formula shows text, start number and number chart

(a) Once the start number which is the same as the Column number is A23, type A23 to highlight the cell meaning that you are ready to extract a portion of data from the cell.

(b) Add a comma and type 6 as the start number; fifth (5^th) number from the beginning

(c) Add a comma and type 5 as the chart number because the name is counted five (5) letters or type 6 just like the start number

(d) Close bracket and use the Enter key to make extraction complete

(e) Scroll down to extract other names in rows below if only they are of the same arrangement and number of letters or do it separately for different arrangements and letters.

     This is the formula: =MID(A23,6,5) to get your extracted middle name: Kwaku

Extracting the surname: Duodu

1.Type an equal sign

2. Type MID function after the equal sign

3. Begin with bracket sign and then the mid formula shows text, start number and number chart

(a) Once the start number which is the same as the Column number is A23, type A23 to highlight the cell meaning that you are ready to extract a portion of data from the cell.

(b) Add a comma and type 6 as the start number; fifth or sixth (5^th or 6^th) number from the beginning

(c) Add a comma and type 5 as the chart number because the name is counted five (5) letters or more than five (5); even 12 or more

(d) Close bracket and use the Enter key to make extraction complete

(e) Scroll down to extract other names in rows below if only they are of the same arrangement and number of letters or do it separately for different arrangements and letters.

   This is the formula: =MID(A23,12,5) or =MID(A23,12,12) to get your extracted surname: Duodu

Note that the chart number for the surname would still be extracted even with any input number beyond 5.

E// WHEN ALPHANUMERIC DATA HAS SAME ARRANGEMENT AND STRUCTURE

MID Function in excel is a text function used to find out strings and return them from any middle part of the Excel. The formula extracts the result from the text or the string itself, the start number or position, and the string’s end position. It takes three arguments as specified below.

=MID(text, start number, number chart) to sort the data. Note that when the alphanumeric data is the same in terms of arrangement and structure, you can scroll below and get the same results for the other alphanumeric data.

EXTRACTING SPECIFIC DATA IN COLUMN (B) 3 – 12 OF THE EXCEL TABLE

1. Type an equal sign in the selected cell
2. Type MID function after the equal sign
3. Begin with bracket sign and then the mid formula shows text, start number and number chart

(a) Once the start number in the table is Column number C3, type C3 to highlight the cell meaning that you are ready to extract a portion of data from the cell.

(b) Add a comma and appropriate number as the start number; that is the number counted from the beginning

(c) Add a comma and type the corresponding chart number to reflect selected data by their counted letters or numbers

(d) Close bracket and use the Enter key to make the extraction complete

(e) Scroll down to get the same results for other cells.

F// WHEN ALPHANUMERIC DATA HAS MIXED/DIFFERENT ARRANGEMENT OF VEHICLE TYPE AND MANUFACTURING YEAR

MID Function in excel is a text function used to find out strings and return them from any middle part of the Excel. The formula extracts the result from the text or the string itself, the start number or position, and the string’s end position. It takes three arguments as specified below.

=MID(text, start number, number chart) to sort the data. Note that when the alphanumeric data is the same in terms of arrangement and structure, you can scroll below and get the same results for the other alphanumeric data.

EXTRACTING SPECIFIC DATA OF COLUMN (C)3 – 12 IN THE EXCEL TABLE

1. Type an equal sign in the selected cell
2. Type MID function after the equal sign
3. Begin with bracket sign and then the mid formula shows text, start number and number chart

(a) Once the start number in the table is Column number C3, type C3 to highlight the cell meaning that you are ready to extract a portion of data from the cell.

(b) Add a comma and appropriate number as the start number; that is the number counted from the beginning

(c) Add a comma and type the corresponding chart number to reflect selected data by their counted letters or numbers

(d) Close bracket and use the Enter key to make the extraction complete

(e) Apply the mid function formula for other cells individually if alphanumeric structures are different or scroll down if alphanumeric structures are the same

G// WHEN ALPHANUMERIC DATA HAS MIXED/DIFFERENT ARRANGEMENT OF VEHICLE ODOMETER READING

MID Function in excel is a text function used to find out strings and return them from any middle part of the Excel. The formula extracts the result from the text or the string itself, the start number or position, and the string’s end position. It takes three arguments as specified below.

=MID(text, start number, number chart) to sort the data. Note that when the alphanumeric data is the same in terms of arrangement and structure, you can scroll below and get the same results for the other alphanumeric data.

EXTRACTING SPECIFIC DATA OF COLUMN (C) 8 – 17 IN THE EXCEL TABLE

1. Type an equal sign in the selected cell
2. Type MID function after the equal sign
3. Begin with bracket sign and then the mid formula shows text, start number and number chart

(a) Once the start number in the table is Column number C8, type C8 to highlight the cell meaning that you are ready to extract a portion of data from the cell.

(b) Add a comma and appropriate number as the start number; that is the number counted from the beginning

(c) Add a comma and type the corresponding chart number to reflect selected data by their counted letters or numbers

(d) Close bracket and use the Enter key to make the extraction complete

(e) Apply the mid function formula for other cells individually if alphanumeric structures are different or scroll down if alphanumeric structures are the same

THANK YOU FOR STUDYING AND LEARNING – NANA KWAKU DUODU