example-1)To extract a number of characters starting from a specified position, the Excel function MID comes into good use.
Syntax: MID(text, start_number, number_of_characters) where:
text is the string(cell# in our case) containing the characters to be extracted,
start number = the position of the first character
number of characters = number of characters
for example: =MID(A2,3,7) means that starting from the character in position#3(third charcter), get the next 7(seven) characters.
Notes:
- the character in position#3 is counted as the first of 7 to be extracted,
- any space/s between the names(in Column-A) is counted as a character.
Results from Column-C show that 7(seven) characters, starting from character#3 are extracted - space between names inclusive.
Results in Column-B are from our first tutorial: http://nairabytes.net/computer-geek/excel-tutorial-how-to-extract-a-number-of-characters-from-a-string
example-2) Suppose all we need is to count the number of characters, we use the LEN(length) function.
Syntax: LEN(string)
formula: = LEN(A2). See below:
Problem to solve:
If we put the above concepts together, let us assume we have a sample problem to solve as follows:
Statement: extract 3-characters where the starting character position is 5-less than the number of characters in each row of Column-A.
Solution: the start_number of the MID function will be the length(LEN) of data in each cell minus-5.
command: =MID(A2,(LEN(A2)-5),3)
Results in Column-E show for ex: "nde" where "n" is the 12th character(17-5) of cell A2.