Let’s face it importing can be a bit intimidating. EasyREImport creates EXACT import files with correctly named and formatted headers that are ready to import with NO Exceptions. But that is only the start, using our Second Pass feature you can take the most complex imports and break them down to manageable easy to work with files that all begin with the ever important constituent import id.
On the page below we have added some of the best Excel tips to get your data ready to import. Do you have data in ALL CAPS or need to combine multiple field? Over 20 years of importing these are some of the best Excel formulas to use when importing.
Unsure of the proper format for importing additional data head over to Blackbaud’s Knowledgebase and search the knowledgebase. They have every import format and sample files. These come in handy when you are trying to import a field you have not imported before, or you have imported it but it was so long ago you forgot.
Have a favorite Excel file we should add? Contact us via our contact form.
Don’t forget our Second Pass feature can take a giant dataset with a large number of columns and turn it into small easy imports.
Favorite Excel FOrmulas
These are a few of the goto Excel formulas we routinely use to reformat files to get the data cleaned up before we can import it. No one wants to import bad data.
The first two formulas you can search Google or YouTube for videos on usage the third is a formula that has come in handy in the past.
- Excel PROPER formula properly cases data from all caps to case sensitive. But watch out for records like Mr. McWrong as the PROPER formula will change his last name from MCWRONG to Mcwrong. You might have this issue with some address values. But you should be able to review your data after running the formula and find any mistakes.
- Excel CONCATENATE can combine any two columns of data. EasyREimport combines two address lines but if you have other columns in your spreadsheet you need to combine the CONCATENATE formula is a great tool.
- EXCEL Parse Last, FirstName MiddleName – This formula works pretty well for extracting the last, first and middle names from a column that has them all together with the last name separated a comma.
This assumes your data is in Column A row 1.
=IF(IFERROR(FIND(” “, A1, SEARCH(” “,A1)+1),), TRIM(MID(A1,SEARCH(” “,A1), SEARCH(” “,A1,SEARCH(” “, A1) + 1) – SEARCH(” “,A1) ) ), TRIM(REPLACE(A1, 1, SEARCH(“,”, A1, 1), “”)))
The above formula extracts the first name “Aimy” from “Smith, Aimy A”. It works 99 percent of the time as long as the names are separated a comma and a space e.g. “, “. However, there are some complicated names that it does not work on such as: “Doe Jr., Rudy E.”
=IF(IFERROR(FIND(” “, A1, SEARCH(“, “, A1)+1), ), TRIM(MID(A1, SEARCH(“, “, A1) + 1, SEARCH(” “, A1, SEARCH(” “, A1) + 1) – SEARCH(” “, A1))), TRIM(REPLACE(A1, 1, SEARCH(“, “, A1, 1), “”) ) )
The above formula extracts “John” from “Van Brown, John B”. It works to get the first name from names with spaces in the last name.
=IF(IFERROR(FIND(” “, A1, SEARCH(” “,A1) + 1),), RIGHT(A1, LEN(A1) – SEARCH(” “, A1, SEARCH(” “,A1) + 1)), “”)
The above formula extracts the middle name “A” from “Smith, Aimy A”.
The last one above extracts the last name, “Doe”, from “Doe, John G”