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

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”

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”

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.

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.

• 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.

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