Performing Numeric Operations on Substrings in Microsoft Excel 2010

 

If we need to perform numeric operations on substrings, we need to use the LEFT, FIND and MID functions in Microsoft Excel 2010.

FIND:This function returns the location number of the character at which a specific character or text string is first found, reading left to right (not case-sensitive).

Syntax of “FIND” function:           =FIND (find_text,within_text,[start_num])

Example:Cell A1contains the text “Broncho Billy Anderson”

=FIND (“Billy”, A1, 1), function will return 9

img1

 

LEFT: Returns the specified number of characters starting from the left-most charcter in the string.

Syntax of “LEFT” function:            =LEFT (text,[num_chars])

Example:Cell A1contains the text “Broncho Billy Anderson”

                        =LEFT (A1, 7), function will return “Broncho”

img2

MID:Returns a specific number of character(s) from a text string, starting at the position specified based on the number of characters specified.

Syntax of “MID” function: =MID (text,start_num,num_chars)

Example:Cell A2 contains the text “Broncho Billy Anderson”

                        =MID (A1, 8, 7), function will return “Billy”

img3

Let’s take an example to understand how we can perform numeric operations on substrings.

Example 1: We have a list in column A. Weneed to perform some numeric operations on this list.

img4

Follow the below given steps:-

  • Select the cell B2, write the formula.
  • =LEFT(A2,FIND(“/”,A2)-1)+10&MID(A2,FIND(“/”,A2),255)
  • Press Enter on your keyboard.
  • The function willadd 10 to the substring in the cell.

img5

  • To copy the formula to all cells, press the key  “CTRL + C”  and select the cell B3:B6 and press the key “CTRL + V” on your keyboard.

img6

This is how we can perform numeric operations on substrings in Microsoft Excel 2010.

 

 



Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>