Setting a text qualifier when importing data into Excel

If you try to analyze a load file in Excel 365, you may have noticed that you don't get the option to set the text qualifier. When you imported a delimited text file, or .csv file, the previous versions of Excel would let you choose a specific text qualifier - a character that set off text fields between delimiters so that when the same delimiter, such as a comma, was used in within an imported field, an error would not result.

However, if you try to import a load file into Excel 365, by using 'From Text/CSV' or 'Get Data . . . From File' this option will be missing:

You can set a delimiter but not a text qualifier. If you want to import data into Excel with the old options, go to File . . . Options . . . and check off the option for 'From Text (Legacy)'.

Now when you go to Data . . . Get Data . . ., you'll see an option for Legacy Wizards:

. . . this will allow you to import data using the old wizard which gives you the option to set a text qualifier.

Sadly, Excel doesn't allow you to enter a custom text qualifier.

Discard CONCAT and use TEXTJOIN

Don't miss how the new TEXTJOIN formula available in MS Excel 365 is an improvement over the old CONCAT formula.

TEXTJOIN gives you the option to add delimiters in between a combined range of cells, and choose what to do when there are empty spaces in the range.

The beginning of the formula begins with the delimiter that you select:

. . . this is followed by TRUE or FALSE - TRUE stops empty spaces from being included - FALSE will add them in. Conclude the formula with the range you want to combine.

It is possible to combine multiple ranges with TEXTJOIN

=TEXTJOIN("; ",TRUE,A2:F2,A3:F3)

Running proximity searches in Excel

It's possible to run proximity searches in Excel using the below Visual Basic code. It will highlight any cells in which two given terms appear within a number of words that you set. Simply copy the vba code into a new module in the project list:

. . . the macro will run and prompt you to enter the cell in which the search should begin, and then prompt you to enter the first term, the second term, and the highest number of words which should appear between these terms.

This macro will only search for the terms in the order in which you enter them. It will find when the second term is within X number of words after the first term, but not vice versa.

Thanks to will266 for posting this code here.

Sub proximitySearch()

Dim startCell: startCell = InputBox("Enter Starting cell for search; i.e. B2", "Starting Cell")

Dim termOne: termOne = InputBox("Enter first search term", "Search Term 1")

Dim termTwo: termTwo = InputBox("Enter second search term", "Search Term 2")

Dim proximity: proximity = InputBox("Enter maximum distance between terms", "Proximity Value")

Dim rng As Range, r As Range

Set rng = Range(startCell, Range("a" & Rows.Count).End(xlUp))

rng.Interior.ColorIndex = xlNone

With CreateObject("VBScript.RegExp")

.IgnoreCase = True

.Pattern = termOne + "\S*(\s\S+){0," + proximity + "} " + termTwo

For Each r In rng

If .test(r.Value) Then r.Interior.Color = vbRed

Next

End With

End Sub