TEXTJOIN() Function to Combine Text in Excel - Excel Quickie 51

[@mohamedkamal1926]

Very helpful video. Thank you

[@toxiqrox]

Thank you, so easy when you know how. I have not had anything to do with formula and you make it make sense finally

[@TeachExcel]

I'm very glad you think so) And yea, half the battle is knowing it even exists!

[@nahlas.3836]

brilliant as usual👍

[@darrylmorgan]

Wicked Little Function Also Really Handy To Learn TEXTJOIN And CONCAT For Those Who Take The 2019 Microsoft Office Certification....Great Stuff Thank You Sir :)

[@TeachExcel]

You're very welcome! I'm kinda suprised anyone has to learn CONCAT but maybe I just always needed to include a separator so I never had a use for that funciton.

[@wayneedmondson1065]

Nice tips.. thanks for sharing. Thumbs up!!

[@TeachExcel]

Glad you think so! I've got a lot of these new functions in the works)

[@IvanCortinas_ES]

Thank you for the information. All's ok!!

[@TeachExcel]

Glad it works)

[@decg1972]

Hi
Is it possible if you have retail price in one column, For example, simplest way for me to explain
Any figures between 17.01 and 17.50 goes to 17.45
Any figures between 17.51 to 17.99 goes to 17.95
Thank you

[@alimohamedahmed2328]

First of all thank you so much
Second I am using office 2016 , and I couldn't find this formula , could plz help me to fix this problem

[@TeachExcel]

Looks like you neex Excel 2019 or 365. But I have a version of this that you can use in any version of Excel: www.teachexcel.com/excel-tutorial/simple-excel-function-to-combine-text-with-a-separator_1556.html?src=yt_comment

[@subramanirangasamy3320]

Excellent , this is related to Concatenate function. I tried excel 2010 not working any idea.....

[@TeachExcel]

It looks like its only for Excel 2019 and Excel 365. But I have a custom function that you can use on teachexcel: www.teachexcel.com/excel-tutorial/simple-excel-function-to-combine-text-with-a-separator_1556.html?stc=yt_comment

[@fredlevel897]

"Ignore empty" is probably the only real added value of this function but that's one I will keep in mind

[@TeachExcel]

I do like that, but I also really like the fact that it allows a separator between the combined values because that's what I often need when I concat text.

[@jawaharlalreddy]

Sir pl send text joining function using concetenta

[@jonnathanjon1]

Wondering how do you include that as part of a vba which I do not want mention a fixed range?

[@TeachExcel]

I think you mean in a udf like this? www.teachexcel.com/excel-tutorial/simple-excel-function-to-combine-text-with-a-separator_1556.html?src=yt_comment

[@jonnathanjon1]

@@TeachExcel
Thanks for the response. Towards the end I have created a text joint formula like this " ActiveCell.FormulaR1C1 = "=TEXTJOIN("","",TRUE,RC[-1]:R[5000]C[-1])" Please help me to create a formula which dynamically select the range and text join instead of saying R[5000]C[-1]. thank you.
Sub TrimwithRemoveDup()
Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Dim mycell As Variant
Dim rSelection As Range
Dim ws As Worksheet
Dim vArray() As Long
Dim i As Long
Dim iColCount As Long
'Check that a range is selected
If WorksheetFunction.Trim(ActiveCell) = "" Then

MsgBox "Current Range is empty.Select valid range.", vbOKOnly, "Warning"
Exit Sub
ElseIf Selection.Count = 1 Then
MsgBox "Please select the range " & Excel.Application.UserName, , "Warning>>"
Exit Sub
End If

'Store the selected range
Set rSelection = Selection
For Each mycell In rSelection
mycell.Value = WorksheetFunction.Trim(mycell.Value)

Next mycell
'Add a new worksheet
Set ws = Worksheets.Add

'Copy/paste selection to the new sheet
rSelection.Copy

With ws.Range("A1")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
'.PasteSpecial xlPasteValuesAndNumberFormats
End With

'Load array with column count
'For use when multiple columns are selected
iColCount = rSelection.Columns.Count
ReDim vArray(1 To iColCount)
For i = 1 To iColCount
vArray(i) = i
Next i

'Remove duplicates
ws.UsedRange.RemoveDuplicates Columns:=vArray(i - 1), Header:=xlGuess

'Remove blank cells (optional)
On Error Resume Next
ws.UsedRange.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlShiftUp
On Error GoTo 0

'Autofit column
ws.Columns("A").AutoFit

'Exit CutCopyMode
Application.CutCopyMode = False
Application.ScreenUpdating = True
ActiveCell.Offset(0, 1).Select

ActiveCell.FormulaR1C1 = "=TEXTJOIN("","",TRUE,RC[-1]:R[5000]C[-1])"
ActiveCell.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Application.SendKeys "{F2}"
Application.SendKeys "^a"
Application.SendKeys "^c"
Application.SendKeys "~"

End Sub