11 Powerful Excel Formulas to Clean Your Real Estate Database

By Reggie · August 17, 2007

Have you ever had the need to reformat a column of data in Excel? Possibly changing how the names read for a mailing list, or reformatting an address. If so you know that the right formulas can save you a great amount of time and frustration. I have 11 powerful excel formulas that will surely help out. Bring the power of Excel’s technology to your desktop.

1. To extract the first name of a cell: type the following formula in B1 (Results: John)
   =LEFT(A1,FIND(” “,A1))
   

2. To extract all of the text after the first word: type the following formula in B2 (Results: L. Smith)
   =MID(A2,FIND(” “,A2,1)+1,LEN(A2))
3. To extract the last word in a cell: type the following formula in B3 (Results: Smith)
   =RIGHT(A3,FIND(” “,A3))
   
4. To change the cell to all lowercase: type the following formula in B4 (Results: john smith)
   =LOWER(A4)
   
5. To change the cell to title case: type the following formula in B5 type the following formula (Results: John Smith)
   =PROPER(A5)
   
6. Change the cell to upper case: type the following formula in B6 (Results: JOHN SMITH)
   =UPPER(A6)
   
7. Takes everything to the left of the comma: type the following formula in B1 (Results: Anytown)
   =LEFT(A1,FIND(“,”,A1)-1)
8. Takes two characters after the comma. A perfect formula to extract state from the cell: type the following formula in C1 (Results: CA)
   =MID(A1,FIND(“,”,A1)+2,2)
9. Take the last 5 characters of the cell. If you have zip +4 change the 5 to 10: In cell D1 type the following formula (Results: 99999)
   =RIGHT(A1,5)



10. This formula will join two cells with a space in between: In cell C1 type the following formula (Results: John Smith)
    =A1&” “&B1
    
11. Say you have first owner name and second owner name in the next column. You may only have second owner names for half of the list. With the standard concatenate function, you will have an “&” after each name whether or not there was a 2nd owner name. With my formula it will only add the “&” if there is a 2nd owner name. So this works conditionally checking for text in the 2nd cell. : In cell C1 type the following formula (Results: John & Amy)
    =MID(IF(ISTEXT(A2),”"&A2,”")&IF(ISTEXT(B2),” & “&B2,”"),1,100)

To use the formula to populate the entire column you will need to use the “Fill” command. For that see the following graphic:

Once you have finished cleaning and scrubbing your data, you still have one more thing to do. You need to keep the column of data you just created, but remove the formula that was used to generate it.

Begin by highlighting the column of data you just created… column “B”. Right mouse click on the column heading and choose “Copy”. Then right click on the same column heading “B” and select “Paste Special” then select “As Values” and click “OK”.