Category Archives: TEXTJOIN

New Excel #2 – Comparing String to Determine if They Are Anagrams Using Dynamic Arrays

One of my previous posts described a methodology for determining if two strings were anagrams – that is, if they contained the same letters in the same frequency, but in a different order.

https://dhexcel1.wordpress.com/2017/04/10/excel-identifying-if-a-string-is-the-anagram-of-another-string-using-the-textjoin-function-by-david-hager/

If you read that article, you will see that several complex formulas were used in the solution to that challenge. However, the following formula made with two of the new dynamic array functions (SORT and SEQUENCE) provides a simple solution.

=TEXTJOIN(“”,,SORT(MID(str,SEQUENCE(LEN(str)),1)))

So, if the variable is used with these two strings (str1 = “hardest” and str2 = “thesard”) in this formula, both return the same result string, “adehrst”, which is the sorted string for each. Thus, the following formula would return TRUE, indicating that they are anagrams.

=TEXTJOIN(“”,,SORT(MID(str1,SEQUENCE(LEN(str1)),1)))Β  =

TEXTJOIN(“”,,SORT(MID(str2,SEQUENCE(LEN(str2)),1)))

Stay tuned for more posts using the dynamic arrays!

#Excel Data Validation – Non-Contiguous Ranges and Changing Data Validation List after Picking

I recently saw this challenge for creating a data validation list from 2 non-contiguous ranges.

https://www.sumproduct.com/blog/article/monday-morning-mulling-december-challenge

Then, while looking up current information about data validation tricks, I reread this post on Debra Dalgleish’s Excel site, which showes a way to change the data validation list based on items picked.

http://www.contextures.com/xlDataVal03.html

I decided that I would try to combine both of these techniques, while at the same time creating the required data validation list without the need for helper columns. When I started on this, I was not sure that it would be possible, but that is the kind of challenge I like 😊.

I had previously published a method for combining non-contiguous ranges into a comma-delimited string.

https://dhexcel1.wordpress.com/2017/05/21/excel-short-and-sweet-tip-15-using-textjoin-with-non-contiguous-ranges-by-david-hager/

Using this technique along with modifying the ranges to exclude blank values, the following formula produces a delimited string combining the elements of two ranges named List1 and List2, as shown in the figure.

DV_PIC1

TJ_TLists =TEXTJOIN(“,”,TRUE,IF(ISBLANK(List1),””,List1),IF(ISBLANK(List2),””,List2))

It is important to note here that any number of ranges (rectangular, non-contiguous or 3D) can be combined in this step to afford the data validation list in the final step. As an example, see:

https://dhexcel1.wordpress.com/2017/05/23/excel-native-3d-ranges-with-the-textjoin-function-plus-bonus-by-david-hager/

The next formula converts this delimited string into an array.

CombinedDV =TRIM(MID(SUBSTITUTE(TJ_TLists,”,”,REPT(” “,999)),ROW(INDIRECT(“1:”&LEN(TJ_TLists)-LEN(SUBSTITUTE(TJ_TLists,”,”,””))+1))*999-998,999))

Unfortunately, an array cannot be used directly as a data validation list. But, since there is more work to do to create data validation that can be used as a pick list, the following formulas are needed.

MatchArr=IF(ISNA(MATCH(ROW(INDIRECT(“1:”&COUNTA(CombinedDV))),MATCH(SelectDV,CombinedDV,0),0)),CombinedDV,””)

affords {“”;”b”;”c”;”d”;”e”;”f”;”g”;”h”;”I”;”j”;”k”;”l”;”m”;”n”;””}

MatchRow=IF(ISNA(MATCH(ROW(INDIRECT(“1:”&COUNTA(CombinedDV))),MATCH(SelectDV,CombinedDV,0),0)),ROW(INDIRECT(“1:”&COUNTA(CombinedDV))),””)

affords {“”;2;3;4;5;6;7;8;9;10;11;12;13;14;””}

Then in cell H2 is entered the formula =INDEX(MatchArr,SMALL(MatchRow,ROW()-1)),which is filled down until a formula returns an error. This is the range to be used as a data validation list.

As shown in the figure, the range where data validation is applied

SelectDV=DVSheet!$B$2:$B$16

contains an β€œa” and an β€œo”.

Then, the formula used for the data validation list is

DVList=DVSheet!$H$2:INDEX(DVSheet!$H$1:$H$16,MATCH(TRUE,ISERROR(DVSheet!$H$1:$H$16),0)-1)

So, when the data validation is used in its current state, the list will not contain those two letters.

I hope that you find this useful.

The example file can be downloaded here.

DV_TwoLists

#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

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

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

CHAR(64+ROW(INDIRECT(“$1:$26”)))

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

{1;1;24;#N/A;4;#N/A;1;#N/A;#N/A;24;#N/A;25;#N/A;26;#N/A;#N/A;#N/A;3;#N/A;5;#N/A}

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

AAXD AXYZ CE

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

Function ArrayFromSDS(MyString As String)

ArrayFromSDS = Split(MyString, ” “)

End Function

produces this array of words.

{“AAXD”,”AXYZ”,”CE”}

HTH!

 

#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.

x,da.”xyz”.c?e!

The following array formula removes those symbols

=TEXTJOIN(“”,TRUE,IF(ISERROR(MATCH(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1),{“,”;”.”;”?”;”!”;””””},0)),MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1),””))

and affords the desired string shown below.

xdaxyzce

HTH!

#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

=INDEX(arr,MODE(MATCH(arr,arr,0)))

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

https://m.youtube.com/watch?v=yorGlCrfqY0  

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.

=TEXTJOIN(CHAR(10),,MID(A1,{1,4,7,10,13},3))

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