You want a formula to return the the username of the current user working on the excel file.
me@jaykilleen.com wrote this about 4 years ago and it was last updated about 4 years ago.
I have used this alot in my Excel projects. Sometimes I do this via vba if I have an .xlsm workbook to begin with. Other times, I don't want to convert to an macro enabled workbook purely to get the username of the person working on the Excel file.
Therefore, I need a formula.
This pairs really well with the Power Query Simple Worksheet Parameter Pattern so that you can bring the current user into Power Query.
The formula I choose to do this is:
This only works for workbooks that are already saved on your computer. ie Not new files or cloud files
The TL:DR of this formula is to return the 8 characters killeenj from *C:\Users\KilleenJ\OneDrive - USG Boral\Documents* which are the 10th to 17th characters in the overall string.
=MID(INFO("DIRECTORY"),LEN("C:\Users\")+1,LEN(INFO("DIRECTORY"))-LEN(MID(INFO("DIRECTORY"),FIND("\",INFO("DIRECTORY"),10),1000))-LEN("C:\Users\"))
Eventually when the LET formula is released, we can use that to store the values in step 2 and 6 like LET("trim_start",LEN("C:\Users\"))
I started breaking down the formula but it gets annoying so I'll finish it later :)
We can break down what is happening with the formula below. Working from the outside in:
=MID(INFO("DIRECTORY"),...,...) returns the current directory or folder like C:\Users\jaykilleen\Documents
=MID(...,LEN("C:\Users\")+1...)Assuming the directory always starts with *C:\Users* we then MID starting at position 10. The length of your directory + 1 (the next character). You can replace this with the some other value if your file lives somewhere else. Note: this formula is also used in start 6.=MID(...,...,LEN(INFO("DIRECTORY")) Gets the total length of characters you can extract which would be 29 (the number of characters in C:\Users\jaykilleen\Documents. This is obviously too many characters, we want to finish at the 17th character which is the last n in C:\Users\jaykilleen.=MID(INFO("DIRECTORY"),FIND("\",INFO("DIRECTORY"),10),1000). This is done by finding the next ** after the one before your username begins. MID starts at 10, finds the next "" then goes for 1000 more characters (which is so many that the string is bound to finish before you hit 1000). This returns **\OneDrive - USG Boral\Documents**.