#Excel: Extracting an Array of Words From a Sentence


The following formula will create an array of words from a string (sentence).

= ArrayFromSDS(TEXTJOIN(“”,,IF(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)=” “,” “,IFERROR(CHAR(64+MATCH(MID(SUBSTITUTE(SUBSTITUTE(A1,”?”,”-“),”*”,”-“),ROW(INDIRECT(“1:”&LEN(A1))),1),CHAR(64+ROW(INDIRECT(“$1:$26″))),0)),””))))

Here is an explanation of how it works.

The array used in the 3rd argument of the TEXTJOIN function starts with the 1st part of the IF formula, shown below.

IF(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)=” “,” “,

which keeps any space from the string in cell A1. The rest of the IF formula


returns only letters from the string in A1. The 1st argument of the MATCH function in this construction,

MID(SUBSTITUTE(SUBSTITUTE(A1,”?”,”-“),”*”,”-“), ROW(INDIRECT(“1:”&LEN(A1))),1)

is very similar to the 1st part of the IF function, but it has one important difference.

Instead of using the string from cell A1, the formula SUBSTITUTE(SUBSTITUTE(A1,”?”,”-“),”*”,”-“) is used instead. The reason for doing this is that the MATCH function recognizes the * and ? symbols as wildcard searches. So, if the 2nd argument of the MATCH function does NOT contain a * or ?, the character “A” will be returned instead (if not removed from the core string).

The 2nd argument of the MATCH function is


which returns an array of letters from A TO Z.

The result of the MATCH function is an array with numbers from 1-26 for positions in the string with letters and “” if not. For example, if the string in A1 is “ AAx,d a.”x~y*z”. c?e! ”, then the MATCH array will return


This array is particularly useful in this specific case, since the numbers can be converted to the letters in the string by using the CHAR function (along with the IFERROR function to turn errors to an empty string). That converts the array to

{“A”;”A”;”X”;””;”D”;” “;”A”;””;””;”X”;””;”Y”;””;”Z”;””;””;” “;”C”;””;”E”;””}

Now, this array can be used as the main argument in the TEXTJOIN function to afford the string


Now, using the ArrayFromSDS user-defined function (shown below)

Function ArrayFromSDS(MyString As String)

ArrayFromSDS = Split(MyString, ” “)

End Function

produces this array of words.





#Excel: Remove Multiple Characters From a String Using The TEXTJOIN Function and Without Using the SUBSTITUTE or REPLACE Function


There are a number of examples of the removal of characters from a string which utilize nested SUBSTITUTE or REPLACE functions. However, they are hard-coded in that the formulas are built with a set number of characters to remove based on the times that the SUBSTITUTE function is used. The formula methodology I am presenting here is more flexible and robust than previous solutions.

In this example, I am trying to remove all punctuation from a string, specifically the one shown below from cell A1. You will note that this string contains five different punctuation symbols, several occurring more than once.


The following array formula removes those symbols


and affords the desired string shown below.



#Excel: Most Frequent Item in a List of Delimited Strings


rng is a defined name range on the worksheet with each cell containing delimited strings. Although it does not necessarily have to be a 1-column list, most examples of delimited strings in a range are of this type. To convert this range to an array, use the following formula.

Define arr as =ArrayFromCDS(TEXTJOIN(“,”,,rng))

where the VBA UDF is shown below.

Function ArrayFromCDS(MyString As String)

ArrayFromCDS = Split(MyString, “,”)

End Function

So, arr is a 1-D array of all of the delimited values from each cell of the range. Then, use this formula


to return the most frequent item.


Insert a Line-Feed Between Every 3rd Character Using an Excel Worksheet Formula


Per an Oz Du Soleil post on how to separate 5 area codes in a single 15 character string with line breaks using Power Query


here is a way to do the same thing using a worksheet formula.

With the string in cell A1. Enter the following array formula in a cell.


Make sure that you select word wrap enabled for the cell containing the formula.


Boolean Formula for Repetitive Characters in a String

  • My answer to a Chandoo challenge. Not exactly the question most are answering, however. More robust. So, the string “hellp” (in cell A1) would return TRUE but “helpl” would return FALSE. I chose the degree symbol (Alt 0176) as my unlikely used twice in a row character, but you can choose another.

=NOT(ISERROR(MATCH(TRUE,CODE(MID(A1,ROW(OFFSET($A$1,,,LEN($A$1))),1))*2=CODE(MID(A1&”°”,ROW(OFFSET($A$1,,,LEN($A$1))),1))+CODE(MID(A1&”°”,ROW(OFFSET($A$2,,,LEN($A$1))),1)),0)))  CSE


#Excel Short and Sweet Tip #29: Inserting Icons Using a User-Defined Function

Disclaimer: You need the Excel version included in Office 365 for this technique to work.

The insertion of icons in Excel 2016 is accomplished from the ribbon by selecting Insert, Icons. There are a number of catagories to select from, as shown in ths figure.


However, recently I have been interested (obsessed?) with worksheet UDFs and their ability to invoke actions or shapes. In this case, I wanted to see if a UDF would insert an icon into the worksheet. This is the VBA function I made with help of the macro recorder. Place this in a general module in your worksheet.

Function MakeIcon(fName As String)

iString =        “https://hubblecontent.osi.office.net/ContentSVC/Content/Download?provider=MicrosoftIcon&

fileName=” & fName & “.svg”

ActiveSheet.Pictures.Insert iString

End Function

Now, enter the formula =MakeIcon(“Man”) in cell A1 and you will get the following result.


Unfortunately, there are several inherent Excel limitations that prevent the full utilization of this function. First, you have to know the correct name of the icon to produce it. It would be nice if Microsoft provided a list of the icon names, but I could not locate one. Then, I tried to get names by macro recording the insertion of multiple icons, but only the “last” selected icon URL is recorded. Even so, I hope that this technique is useful to you.

The example file can be downloaded here.



Generating a “Realtime” Voice Alert for the Latest Magnitude 5 or Greater Earthquake


I recently published an article about getting information on the latest earthquake of magnitude 5 or greater.


Please read this article to see how the core model was constructed.

One problem with this model is that since Excel’s web functions are non-volatile, a formula containing those functions must be recalculated by reentering the formula. I decided that an easier way was needed to trigger an update. I also recently published an article which utilized the hyperlink rollover technique.


I figured that this might be a good way to trigger a recalculation. And, since I was going to use a VBA function to be called from the hyperlink formula, I thought that adding audio functionality would be useful as well. Here is the hyperlink rollover formula used (in cell D5, named Recalculate). Since a rollover is required, the technique is not truly realtime.


And, here is the VBA function called by “rolling over” (passing the cursor over) that cell.

Function EarthQuakeAlert(Optional Person As String = “Him”, _

Optional Rate As Long = 1, Optional Volume As Long = 80)

Static xlApp As New Excel.Application

Dim Voc As SpeechLib.SpVoice

Set Voc = New SpVoice

Dim sAddress As String

‘Application.Volatile True


If Range(“d1”).Value = Range(“b3”).Value Then

MsgBox “No new earthquake > 5.0”


With Voc

If Person = “Him” Then

Set .voice = .GetVoices.Item(0) ‘male

ElseIf Person = “Her” Then

Set .voice = .GetVoices.Item(1) ‘female


End If

.Rate = Rate

.Volume = Volume

.Speak “New Earthquake Alert! ” & Range(“b5”).Value

End With

Range(“d1”).Value = Range(“b3”).Value

End If

EarthQuakeAlert = “Recalculate”

Set xlApp = Nothing

End Function

In order to use SpeechLib.SpVoice in the code, the correct reference (from Tools, References) must be added to the VBE as shown in the following figure.


In this figure is a picture of the earthquake model.


I hope that you find this useful. You can download the file here.