Performing Numeric Operations on Substrings in Microsoft Excel 2010

Follow by Email
Facebook
Facebook
Google+
http://www.exceltip.com/excel-text-formulas/performing-numeric-operations-on-substrings.html
SHARE




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.

 

 

Please follow and like us:
0


Leave a Reply

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

To avoid automated spam,Please enter the value *

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>