Excel formula to extract email addresses

Exceljet has a formula posted here, which is effective at extracting email addresses, and other strings, from text excerpts.

=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),MAX(1,FIND("@",SUBSTITUTE(A2," ",REPT(" ",99)))-50),99))

The formula works by pulling any word which includes the character that is a subject of the FIND formula, which in this case is the '@' symbol. It works by adding spaces to the target cells - so this part of the formula:

SUBSTITUTE(A2," ",REPT(" ",99))

. . . adds 99 spaces around each word:

We SUBSTITUTE blank spaces in A2 with a space 99 times using the REPT formula. (The REPT formula just repeats whatever string you enter for a set number of times. I.e., =REPT("cat",10) gives: catcatcatcatcatcatcatcatcatcat). The number of spaces that are added sets a limit on the length of the string that can be pulled. This solution will not pull a word more than 100 characters in length.

The FIND formula then finds where in the cell the "@" or searched for term appears after the spaces have been added.

=FIND("@",SUBSTITUTE(A2," ",REPT(" ",99)))

The MID formula then extracts the text with the searched for term and the spaces around it:

=MID(SUBSTITUTE(A2," ",REPT(" ",99)),MAX(1,FIND("@",SUBSTITUTE(A2," ",REPT(" ",99)))-50),99)

The TRIM formula then simply removes the extra spaces.

We can extract full words which contain the searched for string because the complete formula surrounds them with buffer blank spaces. They are then easy to target - once the position of one character or segment is located, it's easy to extract the full word and then cut off the extra spaces.

The formula will also successfully locate words, or parts of words and turn the full word in which the segment appears.

A #VALUE error will be given if the searched for text does not appear in the target cell.

Regex to change case of letters

Note that you can easily use a regular expression search to capitalize certain strings in text. In this example we have a list of names, and we want to capitalize the last names of each person. Using NotePad++, we target strings with a first initial and then a period and a space ([A-Z]{1}\. ) and then in the second group any word containing uppercase or lowercase letters of any length ([A-Za-z]*). For search group # 1 enclosed in parentheses, the letter range is listed in the brackets, and then the letter count for the word is given in braces {}. We escape the period with a backward slash because a period has a separate meaning in regex. For the second group, we search for any uppercase or lowercase letter, with the asterisk signifying any number of characters.

([A-Z]\. )([A-Za-z]*)

In the replace field we can then reference the first group and the second group as \1 and \2. Adding \u before the second group signifier will capitalize the first letter.

\1\u\2

Switching \u to \l will make the first letter of the word in the second group lowercase.

Troubleshooting Problems with a printer network connection

Recently while working on a trial, I had trouble sending jobs to a copier/printer which was set up in a hotel for a trial. Jobs sent from a virtual desktop usually didn't work, and even jobs sent locally from my laptop also often failed. I was able to solve the problem by updating the driver, and also by manually inputting the IP address for the printers. These are the steps that I followed.

1. In Windows, I located the printer (just search for Printers and Scanners, select the printer and click manage) and entered 'Printer Properties'.

2. Under Properties, on the Advanced Tab, I had to reset the driver to that for the Konica Minolta printer. It had been changed to 'Microsoft IPP Class Driver'. Click Apply after selecting the right driver.

3. Next, I went to the Ports tab, and then selected ‘Add Port’. Next I selected ‘Standard TCP/IP Port’ and then clicked ‘New Port’

4. In the wizard that came up, I entered the IP address for the copier/printer.

5. After going through the wizard and finishing, I clicked apply and then went to printing preferences for the printer. It's a good idea to set the preferences for the stapling and hole punching functions I was most likely to use while in the virtual desktop. In the virtual environment, these options were disabled.