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”
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
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:
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
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.
We want to calculate the total number of seconds represented by each string in column A.
Follow below given steps:-
This is the way we can convert the text that represents minutes and seconds into numerical values in Microsoft Excel.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
Hi, I have data which contains time in a text for e.g: 10h 3m 20s. I'd like to know if there is a way to convert this into a Time format, e.g: 10:03:20. Thank you