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 remove the first character of this cell

Not working for you?

Remember to replace A1 with the cell that contains the string of words you want to parse out.

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 can seem a bit over-whelming. It is the 4 occurrences of the number 3 that determines that we parse out the 4th word. In other words, to get the 5th word all occurrences of the number 3 would need to be changed to number 4. If it was the 2nd word we wanted, we would change all occurrences of the number 3 to number 1.

Please note, the above formula cannot be used to get the first or last word. You can find the formula for extracting the first word here and the formula for extracting the last word here.

Not working for you?

Remember to replace A1 with the cell that contains the string of words you want to parse out.

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?

Remember to replace A1 with the cell that contains the string of words you want to parse out.

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 contains the string of words you want to parse out.

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 it can be very time consuming.

To display the A-Z series without typing them manually in Excel, you can use the “char” function. Enter the following formula in cell A1 and drag it all the way to cell A90.

=char(row())

The result?

You will notice cell “A65″ to “A90″ will display the A-Z letters.

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 contains the string of words you want to parse out.

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 you want to parse out.

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 word in a cell in this

Not working for you?

Remember to replace A1 with the cell that contains the string of words you want to parse out.

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

Control+RightArrowRight-hand side of block
Control+LeftArrowLeft-hand side of block
Control+DownArrowBottom of the Block
Control+HomeGo to cell A1
Control+EndBottom right-hand-side of used area

Sheet book movement

Control+PgUpNext sheet
Control+PgDownPrevious sheet
Control+TabNext workbook

Formula editing

Control+ ~Display formulae
F2Edit cell formula
Control+RFill right
Control+DFill down

Insertion / deletion

Control++Insert
Control+-Delete

Other shortcuts

Ctrl+1Format cells, charts, text boxes
Ctrl+Shift+LTurn on/off filters
Alt+ESPaste Special
Alt+ESVPaste Special › Values
Ctrl+TInsert table
F4
Hard code a cell

Posted in Shortcuts | Leave a comment