Saturday, March 16, 2013

How to Extract the Last Word in a Cell


1. Open Excel and click 'File' and 'Open,' browsing to the file of raw data. Highlight the file and click 'Open.'
2. Create a blank column next to the first column of raw data you would like to parse. For example, if column 'A' is the first column of raw data, then right-click on the 'B' and select 'Insert.' This will provide you with a new, blank column.
3. Type '=RIGHT(A1,LEN(A1)-FIND('*',SUBSTITUTE(A1,' ','*',LEN(A1)-LEN(SUBSTITUTE(A1,' ','')))))' into cell B2 and click 'Enter.' This formula breaks down the line of text in cell A1 in the following manner:'LEN(A1)-LEN(SUBSTITUTE(A1,' ',''))' makes a count of the spaces.'SUBSTITUTE(A1,' ','|', ... )' replaces the last space with a '|.''FIND('|', ... )' finds the position of the '|.''Right(A1,LEN(A1) - ... ))' returns all characters after the '|.'
4. Click and hold the bottom-right corner of cell B1 and drag it down in order to pass the formula down to the remaining cells.

Blogger news