Combine Cell Values and Delimiter
In Excel for Office 365, or Excel 2019 and later versions, you can use the TEXTJOIN function to combine text from multiple ranges, quickly and easily.
In earlier versions of Excel, you can use this ConCat user defined function (UDF), to combine (concatenate) text quickly.
With this ConCat UDF, from Excel MVP Rick Rothstein, you can specify a range of cells to concatenate, and the delimiter that you'd like between the joined cell valuesd. For example, use a comma, or semi-colon, or a comma and aspace character
NOTE: You can omit the delimiter, but leave the comma in as a placeholder for that missing argument.
Below the code, you can see examples of how to use the ConCat function on the worksheet.
Function ConCat(Delimiter As Variant, _
ParamArray CellRanges() As Variant) _
As String
Dim Cell As Range, Area As Variant
If IsMissing(Delimiter) _
Then Delimiter = ""
For Each Area In CellRanges
If TypeName(Area) = "Range" Then
For Each Cell In Area
If Len(Cell.Value) Then _
ConCat = ConCat _
& Delimiter & Cell.Value
Next
Else
ConCat = ConCat & Delimiter & Area
End If
Next
ConCat = Mid(ConCat, Len(Delimiter) + 1)
End Function
Install the ConCat UDF
If you have never installed a UDF, the procedure is simple... press Alt+F11 to go into the VB editor, click Insert/Module from its menu bar and then Copy/Paste the above code into the code window that opened up.
This short video shows the steps, and there are detailed written steps on the Add Code to Workbook page.
Use the ConCat UDF
Go back to your worksheet put some text in, say A1, A2, A3, C1, D1, D2 and then type this formula into an unused cell...
- =ConCat("-",A1:A3,C1,"HELLO",D1:D2)
and the contents of those cells (and the word "HELLO") will be concantenated together, in the order shown, with a dash between them.
The delimiter (first argument) is required, but if you want to concatenate cells, cell ranges or text together without a delimiter, you can specify the empty string ("") as the first argument or simply omit it (but still use the comma as a placeholder in order to delineate the argument position).
So, you could concatenate my above example cells and text, but with no delimiter between them, either like this...
- =ConCat("",A1:A3,C1,"HELLO",D1:D2)
or like this (note the leading comma)...
- =ConCat(,A1:A3,C1,"HELLO",D1:D2)
Using the ConCat UDF in Other Workbooks
If you install the ConCat UDF into a workbook, then the function will travel with the file if you distribute it to others.
If you find this ConCat UDF useful and want it available for use on any worksheets that only YOU will work on, just install it in your personal.xls file... just remember, though, if you install it to your personal.xls file and use if from there, then the function will NOT travel with any worksheets you distribute to others (meaning cells using ConCat will produce a #NAME! error on their computers) unless, of course, they install the function to their own personal.xls file as well.
If you want to pursue the personal.xls file route, and you don't now have one, you can find out how to create one, on the Highlight Active Cell page.
Delete Row With Specific Word
Here's an Excel macro that will delete all the rows that contain the specified word in the column that you set it to search.
The code will search for instances where the whole cell is equal to the word(s) that you enter, not partial matches.
Because the code uses the SpecialCells method, it is extremely fast -- much faster than looping through a range of cells.
Sub DeleteRowsWithWord()
Dim Col As Variant
Dim Word As String
Dim str1 As String
Dim str2 As String
str1 = "Which column has the word(s)" _
& vbCrLf _
& "you want to search for?"
str2 = "What is the word whose rows" _
& vbCrLf _
& "you want to delete?"
Col = InputBox(str1)
If Len(Col) > 0 And Not _
Col Like "*[!0-9]*" Then _
Col = Val(Col)
Word = InputBox(str2)
With Columns(Col)
.Replace Word, "#N/A", xlWhole
.SpecialCells(xlCellTypeConstants, _
xlErrors).EntireRow.Delete
End With
End Sub
This code can be used whether there are empty cells in the range being searched or not.
The only important part of the code is the With/End With block.
Tthe code before it is provides but one of many methods of specifying the column to be searched and the word to be searched for.
Run the Macro
When you run the macro, you'll be prompted to enter a column:
and a word whose rows you want to delete:
Delete Rows Without Specific Word
In a reverse of the previous code sample, Rick created this code to delete all the rows where the specified word DOES NOT appear.
The code will search for instances where the whole cell is equal to the word(s) that you enter, not partial matches.
Because the code uses the SpecialCells method, it is extremely fast -- much faster than looping through a range of cells.
Sub DeleteRowsWithoutWord()
Dim Col As Variant
Dim Word As String
Dim LastRow As Long
Dim str1 As String
Dim str2 As String
str1 = "Which column has the word(s)" _
& vbCrLf _
& "you want to search for?"
str2 = "What is the word whose rows" _
& vbCrLf _
& "you do NOT want to delete?"
Col = InputBox(str1)
If Len(Col) > 0 And Not _
Col Like "*[!0-9]*" Then _
Col = Val(Col)
Word = InputBox(str2)
LastRow = Cells(Rows.Count, Col) _
.End(xlUp).Row
On Error Resume Next
With Range(Cells(1, Col), _
Cells(LastRow, Col))
.Replace Word, "#N/A", xlWhole
.SpecialCells(xlCellTypeBlanks) _
.EntireRow.Delete
.SpecialCells(xlCellTypeConstants, _
xlLogical + xlNumbers _
+ xlTextValues) _
.EntireRow.Delete
.SpecialCells(xlCellTypeConstants, _
xlErrors).Value = Word
End With
End Sub
Run the Macro
When you run the macro, you'll be prompted to enter a column (as shown in the previous macro), and the word whose rows you DO NOT want to delete.
NOTE: Enter the word using the upper and lower case that you want in the cells. The macro will use exactly what you type in the input box.
Get the Sample File
To see the code, and how to use the macros and UDF, download the Macros to Work with Text sample file. The zipped Excel file is in xlsm format, and contains the macros and UDF code shown on this page.
About Rick Rothstein
I graduated college as a Math Major and then ended up working for some 32+ years as a Civil Engineer (Road Design) for the New Jersey Department of Transportation. I originally started programming in BASIC on a Texas Instruments TI-99/4 as a hobby.
Eventually I formed a company (FFF Software) with two other gentleman and we sold a couple of TI-BASIC programs commercially for a couple of years. I also sold several articles on programming the TI-99/4 to COMPUTE! Magazine. Once TI went out of the home computer market, I purchased a Radio Shack Model 100 (first truly portable computer) and programmed on it (again, as a hobby) for a couple of years. Again, I sold several articles to a couple of magazines that covered this particular computer.
Before buying my first IBM compatible PC, I owned a Commodore 128 and Atari 520. Once I discovered Visual Basic, I was completely hooked. I continued writing articles on and off across the years, but now exclusively for Visual Basic; and, of course, I used Visual Basic in my workplace to make my job, and the job of my co-workers, easier.
About a year or so before I retired, I decided to start helping others with their programming needs by volunteering to answer questions on various Visual Basic newsgroups. In 2007 or so, I branched out and began answering questions on Excel newsgroups as well. I have been retired for a few years now but my Excel (and now to a lesser extent VB) newsgroup volunteer efforts are still continuing to this very day.
Related Excel VBA Tutorials
Excel VBA -- Adding Code to a Workbook
Combine Text and Formatted Number Video
Last updated: January 26, 2023 3:03 PM