tel: +2348055401551 (sms, whatsapp)
skype: tokunboajewole

Excel Tutorial: how to count and extract a number of characters in a string starting from a specified position

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.

ex2

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:

char length

 

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)

exercise1

Results in Column-E show for ex: "nde" where "n" is the 12th character(17-5) of cell A2

Add comment


Security code
Refresh

I use Linux 24x7

Recent Comments