Author Archives: karen

How to remove the first character of a cell in Excel

For example, you have the following the text in a cell “A1” and you’d like to remove the first character “: “I want to remove the first character of this cell To remove the first character ” from A1, use one of the following formula: =MID(A1,2,LEN(A1)) or =REPLACE(A1,1,1,””) or =RIGHT(A1,LEN(A1)-1) The result: I want to […]

Posted in Extracting words | Leave a comment

How to extract Nth word from a cell in Excel

For example, you have the following the text in a cell “A1” and you’d like to extract the 4th word “extract”: I want to extract the nth word in this cell To extract the 4th word “extract” from A1, use the following formula: =MID(MID(MID(SUBSTITUTE(A6,” “,”^”,3),1,256),FIND(“^”,SUBSTITUTE(A6,” “,”^”,3)),256),2,FIND(” “,MID(MID(SUBSTITUTE(A6,” “,”^”,3),1,256),FIND(“^”,SUBSTITUTE(A6,” “,”^”,3)),256))-2) The result: extract This last one […]

Posted in Extracting words | Leave a comment

How to extract last three words in a cell in Excel

For example, you have the following the text in a cell “A1″: I want to extract the last three word in this cell To extract the last three words from A1, assuming all words have 20 characters or less, use the following formula: =TRIM(RIGHT(SUBSTITUTE(TRIM(A1),” “,REPT(” “,60)),180)) The result: in this cell Not working for you? […]

Posted in Extracting words | Leave a comment

How to extract last two words in a cell in Excel

For example, you have the following the text in a cell “A1”: I want to extract the last two word in this cell To extract the last two words in cell “A1″, use the following formula: =MID(A1,FIND(“@”,SUBSTITUTE(A1,” “,”@”,LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))-1))+1,255) The result: this cell Not working for you? Remember to replace A1 with the cell that […]

Posted in Extracting words | Leave a comment

How to convert time format into decimals in Excel

Looking to convert data in time format into decimals on Excel? Try this formula: =A1*1,440/60 where A1 is a cell containing a time value If A1= 12:35, it returns a value of 12.58.

Posted in Functions | Leave a comment

How to display A-Z without typing them manually in Excel

If we want to display a number series, say 1-100, in column A, we can simply drag the cursor to expand the series. But if we  need to display an alphabetic series, say we require A-Z, in column A, unfortunately dragging will not work. One option is to key each alphabet manually. That works, but […]

Posted in Functions | Leave a comment

How to check if a word is in a cell in Excel

For example, you have the following the text in a cell “A1″: I want to check if this word is in the cell in Excel To check if the word “Excel” is in cell A1, use the following formula: =IF(ISNUMBER(SEARCH(“Excel”,A1)),”Yes”,”No”) The result: Yes Not working for you? Remember to replace A1 with the cell that […]

Posted in Functions | Leave a comment

How to count the number of words in a cell in Excel

For example, you have the following the text in a cell “A1″: Count the number of words in this cell To count the number of words in cell A1, use the following formula: =IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1,” “,””))+1) The result: 8 Not working for you? Remember to replace A1 with the cell that contains the string of words […]

Posted in Functions | Leave a comment

How to remove the last word in a cell in Excel

For example, you have the following the text in a cell “A1”: I want to remove the last word in a cell in this cell To remove the last word in cell “A1”, use one of the following formula: =LEFT(TRIM(A1),FIND(“~”,SUBSTITUTE(A1,” “,”~”,LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1),” “,””))))-1) or =IF(ISNUMBER(SEARCH(” “,TRIM(A1))),TRIM(SUBSTITUTE(A1,TRIM(RIGHT(SUBSTITUTE(TRIM(A1),” “,REPT(” “,99)),99)),””)),A1) The result: I want to remove the last […]

Posted in Extracting words | 8 Responses

Basic Excel Shortcuts

I’m always amazed at how my investment banker friends can build a sophisticated financial model on an Excel spreadsheet – without ever touching their mouse. So I went ahead, researched and practiced using a few basic Excel shortcuts, which I’m going to share here: Cursor movement Sheet book movement Formula editing Insertion / deletion Other […]

Posted in Shortcuts | Leave a comment