top of page

Keep in mind that if you're using your phone's mobile hotspot to get online with a laptop or other device the speed of the connection should improve if you tether uour cell phone. Connecting via a USB cord from the smartphone to the laptop will provide a cabled internet connection. This method can prevent drain on the laptop's battery and will be more secure.

20 views0 comments

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.

74 views0 comments

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.


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

13 views0 comments
bottom of page