When formatting CSV files in Excel, there are a few tricks you can use to speed up the population of cell content by way of Excel formulas. These following example formulas can be used as a basis for generating your CSV file.

### Generating email addresses

Address format required |
smithj@schoolname.school.nz |

Formula |
=LOWER(A2)&LOWER(LEFT(B2))&"@schoolname.school.nz" |

Key |
Cell A2 = Last Name (Smith) Cell B2 = First Name (John) Text string = Email extension (@schoolname.school.nz) |

*This formula takes the Last Name column (lowercased), adds it to the First Name column (lowercased and trimmed to first initial), then adds on the email suffix "@schoolname.school.nz".*

Address format required |
johns@schoolname.school.nz |

Formula |
=LOWER(B2)&LOWER(LEFT(A2))&"@schoolname.school.nz" |

Key |
Cell A2 = Last Name (Smith) Cell B2 = First Name (John) Text string = Email extension (@schoolname.school.nz) |

*The formula takes the First Name column (lowercased), adds it to the Last Name column (lowercased and trimmed to first initial), then adds on the email suffix "@schoolname.school.nz".*

Address format required |
john.smith@schoolname.school.nz |

Formula |
=LOWER(B2)&”.”&LOWER(A2)&"@schoolname.school.nz" |

Key |
Cell A2 = Last Name (Smith) Cell B2 = First Name (John) Text string = Email extension (@schoolname.school.nz) |

*The formula takes the First Name column (lowercased), adds a period (full-stop character) separator, adds the Last Name column (lowercased), then adds on the email suffix "@schoolname.school.nz".*

If the email address format required differs from the exemplars above, the formula can be adjusted using the same logic to produce the desired result.

### Generating the login User IDs

User ID format required |
johsmi |

Formula |
=LOWER(LEFT(B2,3))&LOWER(LEFT(A2,3)) |

Key |
Cell A2 = Last Name (Smith) Cell B2 = First Name (John) |

*This formula takes the First Name column (lowercased and trimmed to the first three letters) and adds it to the Last Name column (lowercased and trimmed to the first three letters).*

### Searching for duplicate User IDs

Formula |
=IF(F1=F2,”ERROR”,”OK“) |

Key |
Cell F1 = First user’s User ID Cell F2 = Second user’s User ID Text strings = Message displayed if duplicate found (ERROR) or not (OK) |

*This formula checks to see if two sequentially listed User IDs are identical.*

To use this formula, you must first insert a temporary column after the User ID column (Column F). Then sort the entire spreadsheet by the User ID column (alphabetically). In the temporary column (Column G), insert the formula above into cell G2, then apply the formula to the whole column. If any two sequential User IDs are identical, the corresponding cell beside it in Column G will display “ERROR”. All other User IDs will show as “OK”.

Perform the necessary corrections, and then remember to delete the temporary column when you are done (Column G should in fact contain the user ID numbers in the final CSV file).

## 0 Comments