When it’s simply a character brought in from a webpage.
A department requested that I take the Teams they had created in our LMS and put them into an Outlook Contact list. The LMS had no export feature for lists of teams, but there was a page that lined a nice chunk of data up so nicely – Name, Login, Team Membership. So I grabbed these three columns and went to work in Excel.
The Name column was easily split using Text to Columns into LastName and FirstName columns. I created a column containing the domain for our institution, and then attempted to =CONCATENATE the login column with the domain column. This should have created a beautiful column with the student’s e-mail addresses. Instead, there was a space between the username and the @. So I tried using the TRIM function. No good. I tried a Find and Replace with a space in the Find field and nothing in the Replace field. Failed again. What finally worked was to copy the faux space from the login column and to paste that into the Find field, leaving nothing in the Replace field. Success! (And now, I found a website describing this exact problem & a solution to it. http://www.theexceladdict.com/_t/ebk56mft.htm After I’ve fought out my own solution. Of course.)
I’ve finished off the request with several New Contact Groups in Outlook, where I’ve imported these .csv files and mapped the columns to custom fields in Outlook. So now the department can e-mail their students using whatever account they want.
There. I’ve learned something new & shared it. I don’t know anyone else who does, but I enjoy my Excel wrangling. It gives me a lot of satisfaction in puzzling out how to make the program do what I want.