Skip to content

A5 - Working with Text

Open a new document in Microsoft Excel. Paste in the following list of random names:

Delgado, Mateo
Young, Daniel
Nguyen, Liam
Walker, Mason
Patel, Arjun
Edwards, Hannah
Singh, Amrit
Zimmerman, Leah
Carter, Olivia
Hughes, Ethan
Reyes, Isabella
Garcia, Sofia
Turner, Jack
Xu, Yifan
Foster, Caleb
Ibrahim, Layla
Quinn, Riley
Mitchell, Chloe
Bennett, Lucas
Lopez, Diego
Khan, Aisha
Anderson, Maya
Usman, Zara
Jensen, Noah
Vargas, Elena
O’Connor, Fiona

Remember throughout these steps...

CTRL+Z is your friend! This is the shortcut for undo, and will come in handy.

  1. Find the feature under the Sort tab to sort the names alpabetically.

  2. Split the first and last names by using the Text to Columns features (also under the Sort) tab. Choose delimited and select Comma and Space as delimiters.

  3. Rearrange columns (using copy/paste) so that first names are to the left of last names.

  4. Paste all names into a blank Notepad++ file.

  5. Highlight the TAB character between the first and last name on the first line by double clicking in that space.

  6. Use CTRL+H to bring up the Find/Replace dialog.

  7. The find box should have the TAB character in it. Put just a space into the replace with box, then click Replace All.

  8. Put your cursor right to the beginning of the file.

  9. Go back into the Find/Replace dialog.

  10. Add a dash at the beginning of every line by entering just the ^ character for the find, enter - for the replace with, and set search mode to Regular Expression.

  11. Add a character (or phrase) of your choice to the end of every line by entering just the $ character for the find, and ensuring search mode is set to Regular Expression.

You can see a video demonstration here: https://www.youtube.com/watch?v=nIFR2DP1Ems

Your Assignment

Practice this process a few times until you feel you are fast with it. When you feel confident with it, demonstrate this to me!

Challenge (Optional Extension)

In a new Excel file, paste in the following list:

Toronto - Ahmed, Samir
Vancouver - Chen, Lili
Calgary - Brown, Marcus
Ottawa - Tremblay, Sophie
Edmonton - Singh, Jaspreet
Winnipeg - Kowalski, Marta
Halifax - O’Neil, Patrick
Victoria - Nakamura, Aiko
Regina - Petrov, Ivan

Using similar tecnhiques to what we did above, reformat the data as follows for the entire list:

* Samir Ahmed, Toronto [Student]
* Marcus Brown, Calgary [Student]
* Lili Chen, Vancouver [Student]

If you've done it, show or explain to me how you did it.

Hint - consider adding the comma as a column in Excel prior to pasting data into Notepad++. Excel's fill down feature (CTRL+D) will be helpful here.

Another Optional Extension

Regular expressions can be be powerful!

Learn more about Regular Expressions here: https://regexone.com

Tell me about what you learned.