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.
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.
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 your 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.
In October 2023, the SEC’s Director of Enforcement Gurbir Grewal addressed the New York City Association. See the transcript posted here. In remarks which emphasized the need for businesses to be proactive in complying with financial regulations, the Director stressed the need to preserve electronic data. In the past two years
The SEC has fined more than 40 companies over a $1.5 billion for failing to preserve electronic communications. Most of the noncompliance was a result of employees failing to follow data preservation policies. A key problem was that communications were being conducted outside of official channels.
In December 2021, J.P. Morgan Securities had to pay a $125 million penalty because its employees were communicating about business using text messages, personal email, and WhatsApp, and no steps were taken to preserve the data. The violation was particularly egregious because the individuals responsible for implementing J.P. Morgan’s policies communicated outside of official channels themselves. In this press release, Gerwal warned businesses to, “scrutinize their document preservation processes and self-report failures”. Businesses that find their data preservation processes fall short of the requirements of securities laws are encouraged to report the problem by emailing [email protected].
Gerwal pointed out that the ability of a company to provide the SEC with summaries of financial analyses, locate key documents, and make data custodians available for interviews may lead to a mitigation of the amount of penalties that they are ordered to pay.
Thanks to Amy Sellars of CBRE for pointing out Gerwal’s remarks at yesterday’s ACEDS webinar on the 2023 Legal Industry Collaboration Data Survey.
[View source.]
