Reformatting text as numbers quickly

A frequent problem in Excel is to end up with numbers in a column that are formatted as text. If this happens, a formula making reference to the values in the column may not perform correctly - it will not find the numbers.

Excel gives you a head's up the numbers are present by appending a green tab to the upper left of the cell. When the cell is clicked on you are given a drop down menu in which one of the options is to convert the text to numbers.

With a very long column data of it may be very difficult to find when the numbers formatted as text begin, so you can select all of the cells you need to convert. The drop down menu will only appear on cells with numbers formatted as text.

To get around doing a lot of scrolling up and down, use this shortcut. Select the entire column and use the Text to Columns tool on the Data tab. When the tool opens, rather than selecting one of the delimiter or fixed length settings, simply click 'Finish'.

All of the numbers in the column will be reformatted as numbers.

Excel formula to find row number hit appears on

You can use the Excel MATCH formula to find the row number of that a value in a range appears in.

=MATCH(F2,A:A,0)+ROW(A:A)-1

The MATCH formula searches for where the value in cell F2 appears in column A and then the ROW formula returns the row number that value appears on.

Excel formula to extract folder path

The Excel formula posted here by barry houdini, allows you to pull the the folder path in a file path - the path absent the file name. So this formula:

=LEFT(A1,LOOKUP(2^15,FIND("\",A1,ROW(INDIRECT("1:"&LEN(A1))))))

. . . references the file path in cell A1.

For this file path:

C:\MyDocuments\CSuite\Minutes.docx

. . . the formula will pull the path 'C:\MyDocuments\CSuite'.