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.
-
To extract the first name of a cell: type the following formula in B1 (Results: John)
=LEFT(A1,FIND(” “,A1))
- 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)) - To extract the last word in a cell: type the following formula in B3 (Results: Smith)
=RIGHT(A3,FIND(” “,A3))
- To change the cell to all lowercase: type the following formula in B4 (Results: john smith)
=LOWER(A4)
- To change the cell to title case: type the following formula in B5 type the following formula (Results: John Smith)
=PROPER(A5)
- Change the cell to upper case: type the following formula in B6 (Results: JOHN SMITH)
=UPPER(A6)
- Takes everything to the left of the comma: type the following formula in B1 (Results: Anytown)
=LEFT(A1,FIND(”,”,A1)-1) - 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) - 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) - This formula will join two cells with a space in between: In cell C1 type the following formula (Results: John Smith)
=A1&” “&B1
- 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”.
If you have enjoyed MyTechOpinion - Technology for Real Estate, please subscribe to our RSS feed!
Great help Reggie…
Do you have any reccomendations on any CRM software or Outlook plug-ins for Outlook software?
Hi Reggie,
Great examples. For # 10 & 11 you might want to try using the ‘Concatenate’ function. Makes it a lot easier than having to type, & get right, that lengthy input.
Hi Craig,
Sorry, I must have not done a very good job explaining what number 11 does. Say you have owner first name and second owner name in the next column. You may only have second owner names for have the list. With the standard concatenate, 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. So you can see this is much more powerful.
I’ll update my post!
And Brian have you heard of Yubiq? It may be just what you are looking for.
You da man Reggie!
These will definately save me some time in the future.
Thanks,
Jeff
Great balls of fire. Thanks for sharing your gifted talent.
Reggie all i have to say is ” You are a Stud Muffin”
Thanks for the Help
Mike
[...] www softwareag com24 lcd monitor budget pricedestate planning booksseethesigns compet hamster names11 Powerful Excel Formulas to Clean Your Real Estate DatabaseEver wish you could modify your data in excel but don’t know how? Here are 11 great formulas for [...]