Directory Import - Prepare and format the Excel file
Created by Joshua Ort, Modified on Thu, 28 Mar at 5:20 PM by Sandra Serafini
See the tutorial below to prepare the Excel file that is exported from the U.S. Soccer Learning Center. The tutorial shows you how to clean and correct common formatting issues and errors so the Directory is accurate and provides a professional, consistent appearance. A Directory Import template, guide, and list of steps are also included in this article as an attachment.
281 STEPS
1. This tutorial will demonstrate how to prepare your Excel file prior to a Directory Import.
We will use a typical export from the U.S. Soccer Learning Center for this tutorial.
2. It is a good idea to take the original datafile and Save As with the suffix
_RI Import so you have a copy of the original file in case it is needed.
3. Information regarding Licensing and Risk Management are not needed in the import file, so will be deleted.
4. Select these columns and click Delete
5. We can either leave the column names as they are and map them during the import, or rename them to their RefInsight names.
Renaming them now will allow the columns to be auto-mapped during the import process and save some time.
6. Rename the USSF-id column to USSF ID.
The system will remove the dashes between the numbers automatically during the import process.
No other changes are needed for this column.
7. Next, we will format the First Name column into Proper format so only the first letter is capitalized.
Click the column next to First Name to insert a new column.
8. Click Insert
9. We now have a new, empty column next to First Name.
10. Click inside the cell next to the first entry to add a formula that will format the First Name to Proper.
11. Type in =PROPER(, then click on the cell to be formatted, then type ) to close the bracket. In this example, the formula is =PROPER(B2).
Press Enter.
12. Notice the formatting for the first name is now corrected with the first letter capitalized.
Press the Up arrow to return to the cell you just formatted.
13. Double Click the bottom right square to automatically apply this formula to all the rows below it that contain data.
14. This formula will format all the variations seen in the First Name column to PROPER formatting.
15. We will now copy and use the "Paste Special" command to preserve the newly formatted cells into the First Name column as Values instead of formulas.
Copy the new cells using the Copy command or keyboard shortcut.
16. Right Click the cell of the first entry. In this example, it is cell B2.
17. Click Paste Special
18. Click Values
19. We can now delete the column we used to do our formatting correction.
Select the column by clicking it.
20. Click Delete.
These formatting steps will now be repeated for the column Last Name.
21. Select the column next to Last Name by clicking on the column.
22. Click Insert
23. A new empty column now appears next to Last Name.
24. Click inside the cell next to the first entry for Last Name to enter the PROPER formula.
25. Type in =PROPER(, then click on the cell to be formatted, then type ) to close the bracket. In this example, the formula is =PROPER(C2).
Press Enter.
26. Notice the formatting for the last name is now corrected with the first letter capitalized.
Press the Up arrow to return to the cell you just formatted.
27. Double Click the bottom right square to automatically apply this formula to all the rows below it that contain data.
28. This formula will format all the variations seen in the Last Name column to Proper formatting.
29. We will now copy and use the "Paste Special" command to preserve the newly formatted cells into the Last Name column as Values instead of formulas.
Copy the new cells using the Copy command or keyboard shortcut.
30. Right Click the cell of first entry of Last Name. In this example, it is cell C2.
31. Click Paste Special
32. Click Values
33. We can now delete the column we used to do our formatting correction.
Select the column by clicking it.
34. Click Delete
35. The Gender column does not need to be renamed because it already matches what is in RefInsight.
The options for Gender are male, female, non-binary, other, and unknown.
36. If this column has any abbreviations or other entries, replace them at this step.
For example, if "m" is entered, replace these entries with "male". If "f" is entered, replace with "female".
37. The options for Ethnicity are currently being updated in RefInsight to match those from the Learning Center. It will be an importable column in the near future.
No changes are needed to the formatting of this column.
38. The column DOB matches the name in the RefInsight template.
Although the date displays here as "MM/DD/2-digit Year", it will display as "4-digit Year/MM/DD" in the import preview. The format will be indicated during the import mapping step.
39. We will now format the Email column.
Rename the Email column to Email 1 for it to automap.
40. Rename the column either by double-clicking inside the cell or by clicking inside the formula bar.
41. Type Email 1 and Press Enter
42. We will now format the Email 1 column to all lower case.
Highlight the column to the right of the Email 1 column and click Insert.
43. Click inside the cell next to the first entry to enter the formula. In this example, it is cell H2.
44. Type in the formula =LOWER( , then click the cell to be formatted, then type ) to close the bracket. In this example, the formula is =LOWER(G2).
Press Enter.
45. Press the Up arrow to go back to the cell.
46. Double Click the green square at the bottom right of this cell to automatically copy the formula down all the rows that contain data.
47. Note the format is now lower case for all email addresses.
Copy the newly formatted cells using the copy command or your keyboard shortcut.
48. Right Click on the first entry of the Email 1 column. In this example, it is cell G2.
49. Click Paste Special
50. Click Values
51. The newly formatted cells are now in the Email 1 column as values instead of formulas.
The column we used to make the formatting adjustment can now be deleted. Click the column to highlight it.
52. Click Delete
53. The Secondary Email column is usually a parent or guardian email address for minor officials.
Since a unique email address is required for login credentials, many of these emails would need to be removed during the import process.
54. We recommend this column be deleted to save the time it would take to remove them later.
RefInsight collects the parent/guardian email when a minor onboards and stores it separately from the minor official's profile.
55. The parent/guardian email is automatically copied on all communication, such as messaging and notifications, that is sent through the RefInsight platform.
56. To delete this column, highlight it and click Delete.
57. We will now format the Address column.
Rename the Address column to Address Line 1 for it to automap.
58. Click on the Address cell
59. Click inside the Formula Bar to rename the column to Address Line 1 and press Enter.
This column usually requires multiple steps to correct errors and clean up the formatting.
60. One common error is a user entering incorrect or extra information in this datafield, such as email address, City, State, Zip Code, or apartment number.
Visually inspect the column for these errors and manually correct them.
61. In this example, we will delete the City, State, and Zip Code from this entry.
Click on the cell, then click inside the Formula Bar to delete the unnecessary information.
62. In this example, the apartment number was included in Address Line 1 instead of being entered in the Address Line 2 column.
63. You can either delete the apartment information and type it into the Address Line 2 column, or select, cut, and paste the information into the Address Line 2 column.
64. Remember to remove any commas or other errant characters from the cell.
65. Occasionally, ASCII characters will appear when an apostrophe or accent key is utilized by the user in the Learning Center.
Visually inspect the column for these characters and correct them before proceeding further.
66. In this example, we will delete the ASCII characters and replace them with an apostrophe. You can confirm the correct spelling of the address by checking Google Maps.
67. Next, we will format the Address Line 1 column into Proper format so only the first letter of each word is capitalized.
Click the column next to Address Line 1 to insert a new column.
68. Click Insert
69. We now have a new, empty column next to Address Line 1.
70. Click inside the cell next to the first entry to add a formula that will format the Address Line 1 column to Proper.
71. Type in =PROPER(, then click on the cell to be formatted, then type ) to close the bracket. In this example, the formula is =PROPER(H2).
Press Enter.
72. Notice the formatting for the Address is now corrected with the first letter capitalized for each word.
Press the Up arrow to return to the cell you just formatted.
73. Double Click the bottom right square to automatically apply this formula to all the rows below it that contain data.
74. This formula will format all the variations seen in the Address Line 1 column to Proper formatting.
75. We will now copy and use the "Paste Special" command to preserve the newly formatted cells into the Address Line 1 column as Values instead of formulas.
Copy the new cells using the Copy command or keyboard shortcut.
76. Right Click the cell of first entry of Address Line 1. In this example, it is cell H2.
77. Click Paste Special
78. Click Values
79. The Address Line 1 column is now in PROPER format.
80. We can now delete the column we used to do our formatting correction.
Select the column by clicking it.
81. Click Delete
82. Note the previous step may have capitalized abbreviations of street name suffixes, and need to be corrected.
Visually inspect the column and manually correct any of these instances.
83. In this example, we will correct "61St Avenue" to "61st Avenue".
84. In this example, we will correct "137Th St" to "137th St".
85. In this example, we will correct "193Rd Street" to "193rd Street".
86. Continue to inspect the column and make corrections where needed.
87. Here is an entry that needs correcting.
88. Here are other examples needing corrections.
89. Next, we will remove all the periods from the Address Line 1 column.
Click on the column to highlight it.
90. Click Find & Select
91. Click Replace...
92. Type the period character . and leave the "Replace with" field blank to remove the periods.
Click Replace All.
92b. Click
93. Excel will display how many replacements were made.
Click OK
94. The next step will replace long versions of address suffixes with abbreviations through a series of Find and Replace All steps.
Click the column to highlight it.
95. Click Find & Select
96. Click Replace...
97. Type in Avenue in the "Find what" datafield.
97b. Click
98. Type the abbreviation for Ave in the "Replace with" datafield.
Click Replace All.
98b. Click
99. Click OK
100. Repeat these steps for the following: Find Boulevard and replace with the abbreviation for Blvd.
100b. Click
101. If there is no matching data in your datafile, simply click OK to go to the next replacement.
102. Find Court and replace with the abbreviation "C" "t".
Click Replace All, then OK to proceed to the next item.
102b. Click
103. Find Drive and replace with the abbreviation "D" "r".
Click Replace All, then OK to proceed to the next item.
103b. Click
104. Find Lane and replace with the abbreviation "L" "n".
104b. Click
105. Find Parkway and replace with the abbreviation "P" "k" "w" "y".
105b. Click
106. Find Place and replace with the abbreviation "P" "l".
106b. Click
107. Find Road and replace with the abbreviation "R" "d".
107b. Click
108. Find Street and replace with the abbreviation "S" "t".
108b. Click
109. Click Replace All, then OK between each item until finished.
110. Click Close
111. The previous step may have inadvertently replaced parts of words that should not be abbreviated.
A common, but incorrect, replacement occurs with the street name "Broadway", changing it to "BRdway".
112. Visually inspect the column for these types of inadvertent changes and correct them manually.
113. Make additional corrections as needed.
114. Next, we will rename the Apt/Suite/Unit column to Address Line 2 so it will automap.
115. Delete any errant entries in this column, such as "none", "house", or any other entry that is not an apartment or unit.
116.
117. Insert "Apt" or "Unit" where they are missing by clicking inside the formula bar or double-clicking inside the cell.
Use Proper formatting to consistently format the cells in Address Line 2.
In this example, 4a becomes Apt 4A.
118. 6 B is corrected to Apt 6B
119. apt 8e is corrected to Apt 8E
120. We will now format the City column. This column is also called City in RefInsight and does not need to be renamed.
To start, visually inspect the column and remove any extra or erroneous information from cells, such as state or zip code.
121. Double Click inside the cell and use the Delete or Backspace key to remove errant information.
122. Press Enter.
Correct any other cells with incorrect information.
123. Next, we will format the City column into Proper format so only the first letter of each word is capitalized.
Click the column next to City to insert a new column.
124. Click Insert
125. Click inside the cell next to the first entry to add a formula that will format the City column to Proper formatting.
126. Type in =PROPER(, then click on the cell to be formatted, then type ) to close the bracket. In this example, the formula is =PROPER(J2).
Press Enter.
127. Press the Up arrow to return to the cell.
128. Double Click the bottom right square to automatically apply this formula to all the rows below it that contain data.
129. We will now copy and use the "Paste Special" command to preserve the newly formatted cells into the City column as Values instead of formulas.
Copy the new cells using the Copy command or keyboard shortcut.
130. Right Click the cell of the first entry of City. In this example, it is cell J2.
131. Click Paste Special
132. Click Values
133. The City column is now in PROPER format.
134. We can now delete the column we used to do our formatting correction.
Select the column, then Click Delete.
135. It is common for City names to be misspelled by end-users, or have variations of spellings, such as "Saint" vs "S" "t" or "Mount" vs "M" "t".
We will use filters to help identify misspellings and variations so they can be corrected.
136. Click on the column header City, then click Sort & Filter
137. Click Filter
138. Note that filters have now been added to the column headers.
139. Uncheck Select All
140. Visually inspect the list and click the items that will group the correct city name with the incorrect or inconsistent city names.
141. In this example, we see the misspelled City name of Atfon rather than Afton.
Check the boxes next to both items, then click Apply Filter.
142. Double-click inside the cell to edit it.
143. If you prefer, copy a cell with the correct name and paste it to the cell with the incorrect name.
144. Press Escape to continue your inspection. Click the Filter icon.
145. Uncheck your previous filter (Afton) and look for the next correction to be made.
146. We see another example where Deerwood is misspelled as Derwood. Repeat the previous steps to make this correction.
147.
148.
149.
150.
151.
152.
153. Here is another example with incorrect spellings of Minneapolis.
154.
155. Select the correct spelling and any misspellings and click Apply Filter.
156. Find any incorrect spellings.
157. Make corrections as needed.
158. When finished correcting the last item, click the Filter icon.
159. Click Clear Filter
160. Close the filter window by clicking the x at the top left of the window.
161. We will now format the State column. This column is also called State in RefInsight and does not need to be renamed.
162. RefInsight uses the full spelling of the state name to accommodate non-US entries in the Directory.
163. We will use the Find & Replace function to adjust abbreviations to their full spelling.
Select this column and click on the filter to see what entries are in this column.
164. In this example, we see three state abbreviations that need to be replaced with their full spelling.
Click the filter icon to close this pop-up window.
165. Ensure only the State column is selected. Click Find & Select in the menu.
166. Click Replace...
167. Type in the abbreviation of the first state in the Find what datafield. In this example, we will type "I" "N".
167b. Click
168. Type the full spelling of the state in the Replace with datafield.
In this example, we will type Indiana.
169. Click Replace All
170. Click OK
171. Repeat these steps for the other state abbreviations in this column, clicking Replace All and OK between each entry.
171b. Click
172.
173. Here is an example for Minnesota.
174. Type the full spelling, then click Replace All.
174b. Click
175. Click OK.
176. Click Close when finished.
177. Check that all state abbreviations have been replaced, with no misspellings, by clicking on the filter icon.
178. Click the filter icon again to close the filter pop-up.
179. We will now format the Zip Code column.
RefInsight calls this column Postal Code. Rename this column for it to automap during the import.
180. Double-click the Zip Code cell to edit the entry or click the cell and use the formula bar to edit.
181. Rename this column to Postal Code and Press Enter.
182. This column may contain various errors, such as a 4-digit zip code, transposed numbers, or zip code suffixes.
Click the filter icon to identify the entries that need correcting.
183. Uncheck the Select All filter.
184. Check the box next to any 4 digit zip codes. In this example, we will correct 6-3-4-5.
185. Check the box next to any zip codes with suffixes. In this example, we see 5-6-4-4-4-1-2-3-4.
186. Click Apply Filter
187. Using Google maps or from other entries for Little Falls, we know the zip code should be 5-6-3-4-5. Make this correction by double-clicking inside the cell or by using the formula bar to edit.
188. Delete any suffixes after the zip code to adjust to a 5-digit zip code.
189. Press Enter when finished.
190. By visually inspecting other zip codes, we see 5-5-0-0-1 above 5-5-0-1-0. To confirm these refer to different cities or one of the entries is an error, check the boxes next to each.
191.
192. Click Apply Filter
193. Close the pop-up window.
194. Note that the City Afton has several entries with 5-5-0-0-1 as the zip code. The last entry is incorrect, with a 5-5-0-1-0 zip code, and needs to be corrected.
195. Copy and paste the correct zip code into the cell with the incorrect zip code.
196. Press Escape when finished.
197. Click Clear Filter when finished inspecting the zip codes and correcting them where necessary.
198. Close the pop-up window when finished correcting zip code entries.
199. We will now format the Country column.
RefInsight calls this column Country so it does not need to be renamed.
200. The platform will convert most abbreviations for the United States, such as US or USA, but replacing the abbreviation with the full spelling can be completed quickly.
Select the Country column. Click Find & Select.
201. Click Replace...
202. Type in US in the Find what: datafield.
203. Type United States in the Replace with: datafield and click Replace All
203b. Click
204. Click OK
205. Click Close when finished.
206. Formatting the Phone and mobile Phone columns to a 10-digit format is optional. If you choose to format these columns, they should be visually inspected to correct errors.
207. Rename the Phone column to Phone 1 to allow it to automap during the import. Double-click inside the cell to edit.
208. Type in Phone 1
209. Double-click on the mobile Phone cell and change it to Phone 2.
210. If you see an entry with scientific notation, it usually means there is an extra 1 at the start of the entry.
211. Correct the entry by removing the extra 1 at the beginning so it is a 10-digit number.
212. The entry is now a 10-digit number.
213. If you see any entries with the 1 missing at the beginning, add it in. The goal is to have all the entries be a 10-digit number.
214. You can remove duplicate numbers in the Phone 1 and Phone 2 columns if you wish.
215. To identify and remove duplicates, select these columns. Click Conditional Formatting.
216. Click Highlight Cells Rules
217. Click Duplicate Values...
218. Click OK
219. The duplicates will be highlighted in red. Press delete or backspace on your keyboard to remove duplicates.
220. Here is an example.
221. Continue deleting duplicates.
222. When finished, click Conditional Formatting.
223. Click Clear Rules
224. Click Clear Rules from Entire Sheet
225. We will now take several steps to turn the 10-digit number to a standard phone number format (880) 123-4567.
226. Insert a new column next to Phone 1
227. Select the column and click Insert.
Repeat this step to insert a column to the right of Phone 2.
228. Click on the cell next to the first entry. We will replace the preceding 1 with an open bracket (
229. Type in the formula =REPLACE(N2,1,1,"(") and press Enter.
In this example, the cell is N2
229b. Click
230. Double-click the green square at the bottom right of the cell to copy the formula down all the rows that contain data.
231. All rows now contain the formula entered.
232. Copy this formula and paste it next to the Phone 2 entries.
233. Paste it here.
234. Repeat this step for other entries.
235. Continue with remaining entries.
236. Copy the newly formatted cells
237. Right Click the first entry in the Phone 1 column.
238. Click Paste Special
239. Click Values
240. Clear the column to prepare for the next formatting step.
241. Select the cells and press Backspace or Delete on your keyboard.
242. Select the newly formatted entries next to the Phone 2 column and copy them.
243. Right Click the first entry in the Phone 2 column.
244. Click Paste Special, then click Values
245. We will now insert the closed bracket ) and a space after the third number to show the area code in brackets with a space after it.
246. Type in the formula
=LEFT(N2,4)&") "&RIGHT(N2,LEN(N2)-4).
Ensure there is a space after the closing bracket inside the formula.
Press Enter.
247. Double Click the green square at the bottom right of the cell to copy this formula down all the rows.
248. All the rows now contain the formula.
249. Copy the formula next to the entries of the Phone 2 column.
250.
251. Paste the formula next to any entries.
252. Copy the newly formatted cells next to the Phone 1 column.
253. Right Click the first entry in the Phone 1 column.
254. Click Paste Special
255. Click Values
256. Clear the column to prepare for the last formatting step.
257. Copy the newly formatted cells in the Phone 2 column, then right click on the cell of the first entry.
258. Click Paste Special
259. Click Values
260. Clear the contents next to the Phone 2 entries to prepare for the last formatting step.
261. In this final formatting step, we will add a dash before the last 4 digits.
This final step will give us the phone number format of (123) 234-5678.
262. In this example, the cell we are formatting is N2. Type in the formula
=LEFT(N2,9)&"-"&RIGHT(N2,LEN(N2)-9).
Press Enter.
263. Double-click on the green square at the bottom right of the cell to copy the formula down all the rows.
264. All the rows now contain the formula.
265. Copy and paste the formula into the cells next to the Phone 2 entries.
266.
267.
268.
269. Copy the newly formatted cells.
270. Right Click the first entry in the Phone 1 column. In this example, it is cell N2.
271. Click Paste Special
272. Click Values
273. The column used to enter formulas can now be deleted. Select the column.
274. Click Delete
275. Select the newly formatted cells next to the entries in the Phone 2 column.
276. Right click the first entry in the Phone 2 column. Click Paste Special.
277. Click Values
278. We can now delete the column used to enter formulas. Select the column.
279. Click Delete
280. We are in the process of adding Years of Experience to the import and Learning Center API. No formatting changes are needed at this time.
281. Be sure to save your file before closing.
Your Excel file is now ready for import into the Directory.
Thank you for watching the tutorial on preparing your Excel file for Directory import.
Here's an interactive tutorial
** Best experienced in Full Screen (click the icon in the top right corner before you begin) **
https://www.iorad.com/player/2349853/Directory-Import---Prepare-and-format-the-Excel-file
Attachments (1)
Was this article helpful?
That’s Great!
Thank you for your feedback
Sorry! We couldn't be helpful
Thank you for your feedback
Feedback sent
We appreciate your effort and will try to fix the article