668 – Ay Eye Ay Eye, Oh

clip_image002

Not Aye-Aye, Ally-Ally, nor Why-Eye, but Ay-Eye, as in A.I. And not the cheesy Spielberg flick. The tech news has been all about artificial intelligence recently, whether it’s ChatGPT writing essays or giving witty responses, to Microsoft committing another chunk of change to its developer, OpenAI.

Original backers of OpenAI include Tony Stark (who has since resigned from the board in order to discombobulate the world in other ways) and AWS, though Amazon has warned employees not to accidentally leak company secrets to ChatGPT and its CTO has been less than enthused.

clip_image004ChatGPT is just one application – a conversational chatbot – using the underlying language technology that is GPT-3, developed by the OpenAI organization and first released over 2 years ago. It parses language and using previously analyzed data sets, gives plausible-sounding responses.

Further evolutions could be tuned for particular clip_image006tasks, like generating code – as already available in PowerApps (using GPT-3 to help build formulae) or GitHub CodePilot (which uses other OpenAI technology that extends GPT-3). Maybe other variants could be used for interviews or auto-generating clickbait news articles and blog posts.

Another use for GPT-n has been unveiled in the New Bingan old dog has maybe learned some new tricks?

clip_image008You’ll need to join the waitlist initially but this could ultimately be a transformational search technology. Google responded quickly by announcing Bard, though Googling “Google Bard” will tell you how one simple mistake hit the share price. No technology leader lasts forever, unless things coalesce to there being only one.

Other AI models are available, such as OpenAI alternative, Cohere, and there are plenty of sites out there touting AI based services (even if they’re repainting an existing thing to have .ai at the end of it). For some mind-blowing inspiration including AI-generated, royalty-free music or stock photos, see this list.

628 – Text handling in Excel

Excel logoAnyone who has delved into writing formulæ in Excel will probably have had to manipulate strings of text at some point, possibly to clean up formatting or to convert what Excel thinks is a simple block of text into more meaningful data that we know it to be, like a number or a date.

There are simple ways of bulk handling text without resorting to writing a formula – copy all the names from the To: line in an Outlook email, for example: paste into a new spreadsheet and you’ll end up with pasting email addresses into Excela single line of text containing all of the display names and email addresses in one cell, which you may want to split up, to be of much use.

Text to ColumnsSeparate the text into multiple columns by selecting the first cell, then go to the Data tab and look for Text to Columns, which presents a fairly powerful if somewhat old-fashioned looking dialog box, to step through fixing up your text.

In the example above, we have a “;” separating – or “delimiting” – each address, so clip_image008we’ll use that to split the text across multiple columns.

Transpose Paste SpecialSince we might want to create a table of names / addresses, select the cells spread across the columns, copy or cut them to the clipboard, then on a new line below, right-click and look for the Transpose option under Paste Special. Once that’s done, feel free to delete the original top row, or clear the contents of the first cell as we might come back to that row to add column names later.

There is some other cleaning up to do with this text, though; the Text-to-Columns function chopped everything at the “;” but there’s a space which follows the semicolon, so all the Display Names after the first one have a leading space. We could repeat the Text-to-Column feature on the selection again, but use a Space as delimiter now – Preview Text to Columnsunfortunately that would mangle the display names into multiple columns, and if we had a smattering of users with middle names or 3 or 4 part names common in many countries, it could make things look even worse.

Use custom delimiterUsing the leading “<” of the email address as the delimiter is probably simplest, as it will separate the name(s) and email addresses out, though it does still give us a few tidying-up challenges, as there are spaces we don’t want and a trailing “>” at the end of every email address.

In cases like this, it’s easier to use a formula to clean things up – the Trim function being a good place to start; it removes both trailing and leading spaces in string, so the name can be fixed up into a new column.

Since we know the email address has one errant character – that trailing “>” – left behind from the earlier text-to-column operation, there are a variety of ways to strip it off. There’s theclip_image016 =LEFT() function, which keeps the left-most (n) characters of a string – so by combing the LEN function and knocking off a single character, we can chop the final character off.

After all this palaver, you might be thinking that some of this clip_image018chopping around and formulaic string-handling can get a bit confusing as you start to nest operations within each other. Luckily, the Excel team has released some powerful new text-handling functions to try to simplify things a little:

  • TEXTBEFORE – Returns text that’s before delimiting characters 
  • TEXTAFTER – Returns text that’s after delimiting character 
  • TEXTSPLIT – Splits text into rows or columns using delimiters 

TEXTSPLIT functionSo, using TEXTSPLIT on our original pasted text from the email, using “<” as the delimiter for the columns and “>; “ as the marker for the end of each row, gives us a near perfect solution – the only clanger being the trailing “>” on the last address.

You could use another formula to find and strip out any left-over characters like that, or just manually delete the last “>” off the original line you pasted in.

You decide.