Converting Text Representing Minutes and Seconds into Numerical Values in Microsoft Excel 2010

Follow by Email
Facebook
Facebook
Google+
http://www.exceltip.com/excel-date-time-formulas/converting-text-representing-minutes-and-seconds-into-numerical-values.html
SHARE




To convert the text representing minutes and seconds into numerical values, we use “LEFT”, “FIND”, “LEN” and “MID” functions in Microsoft Excel 2010.

LEFT: Returns the first character(s) in a text string based on the number of characters specified.

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

Example: Cell A1contains the text “Broncho Billy Anderson”

                        =LEFT (A1, 7), and function will return “Broncho”
 
img1
 
FIND: This function returns the 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 A1 contains the text “Broncho Billy Anderson”

=FIND (“Billy”, A1, 1), andfunction will return 9
 
img2
 
MID:Return 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”

We want to return the Middle name from the cell A1.

Follow below given steps:

  • Write the formula in cell B1.
  • =MID (A1, 8, 7)
  • Press Enter on your keyboard.
  • The function will return “Billy”

 
img3
 
LEN: Returns the number of characters in a text string.

Syntax of “LEN” function:             =LEN (text)

Example: Cell A1contains the text “Broncho Billy Anderson”

                        =LEN (A1), and function will return 22
 
img4
 
Let’s take an example to understand how we can convert text representing minutes and seconds into numerical values.

Column A contains text string representing the time value in the “XmYs” format. X represent the number of minutes and Y represents the number of seconds.
 
img5
 
We want to calculate the total number of seconds represented by each string in column A.

Follow below given steps:-

  • Write the formula in cell B2.
  • =(LEFT(A2,FIND(“m”,A2)-1)*60)+LEFT(MID(A2,FIND(” “,A2)+1,99),LEN(MID(A2,FIND(” “,A2)+1,99))-1)
  • Press Enter on your keyboard.
  • The function will return the total number of seconds.

 
img6
 

  • Copy the same formula by pressing the key Ctrl+C and paste in the range B3:B6 by pressing the key Ctrl+V on your keyboard.

 
img7
 
This is the way we can convert the text that represents minutes and seconds into numerical values in Microsoft Excel.
 
 

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>