TRIM Formula - Remove Comma from value in cell

[hrayani]

Hi All,

I need a formula to remove commas from a cell...

Sample Data

123, 456, 567,,,,,, required answer 123, 456, 567
990,256,, reqiured answer 990,256
12567, 308211, required answer 12567, 308211

I want all commas to be removed which are at the end of the cell value

Any help would be appreciated

Regards,

Humayun

 

[steve the fish]

This UDF should mimic the TEXTJOIN function:

Function TJOIN(delimeter As String, ignore_blanks As Boolean, range1 As Range)

Dim arr, i As Long, myVal As String

If range1.Cells.Count = 1 Then
    TJOIN = range1.Value
    Exit Function
Else
    arr = range1
    For i = LBound(arr) To UBound(arr)
        If ignore_blanks = True Then
            If Len(arr(i, 1)) > 0 Then
                myVal = myVal & arr(i, 1) & delimeter
            End If
        Else
            myVal = myVal & arr(i, 1) & delimeter
        End If
    Next
End If

If Len(myVal) > 0 Then
    TJOIN = Left(myVal, Len(myVal) - 1)
Else
    TJOIN = ""
End If

End Function

 

[hrayani]

Hi Steve,

Not working.... =TJOIN(A1:A5)

Let me know if i have not input the formula correctly

 

[sandy666]

if you are able to use PowerQuery you can try this

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td][/td][td=bgcolor:#70AD47]Custom.1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

256​

[/td][td][/td][td=bgcolor:#E2EFDA]256, Humayun, Abc[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Humayun[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Abc[/td][td][/td][td][/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table8"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each "a"),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"Count", each _, type table}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.Column([Count],"Column1")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Custom.1", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Removed Other Columns" = Table.SelectColumns(#"Extracted Values",{"Custom.1"})
in
    #"Removed Other Columns"[/SIZE]

 

[hrayani]

Hi Sandy,

Thanks but i absolutely know nothing about power query

 

[sandy666]

so it's time to learn I think
Excel Power Query (Get & Transform) Tutorial for Beginners

 

[hrayani]

Currently i am using the below code.... which is also ignoring duplicates while concatenating

Public Function concatPlus(rng As Range, Optional sep As String = "", Optional noDup As Boolean = False, Optional skipEmpty As Boolean = False) As String'concatenates a range with the specified separator
Dim cl As Range, strTemp As String


If noDup Then


    Dim newCol As New Collection


    On Error Resume Next


    For Each cl In rng.Cells
        If skipEmpty = False Or Len(Trim(cl.Text)) > 0 Then _
            newCol.Add cl.Text, cl.Text
    Next


    For i = 0 To newCol.Count
        strTemp = strTemp & newCol(i) & sep
    Next


Else


    For Each cl In rng.Cells
        If skipEmpty = False Or Len(Trim(cl.Text)) > 0 Then _
            strTemp = strTemp & cl.Text & sep
    Next


End If


concatPlus = Left(strTemp, Len(strTemp) - Len(sep))


End Function

Only problem is a comma sign at the end of the result

For example

A1 = 1
A2 = 1
A2 = 3
A2 = 4
A2 = 5

Formula Entered = =concatplus(A1:A5,", ",TRUE)

Its showing 1,3,4,5,

A comma at the end

 

[sandy666]

I can't help with vba but I can with PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td][/td][td=bgcolor:#70AD47]Custom.1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

1​

[/td][td][/td][td=bgcolor:#E2EFDA]1, 2, 3, 4[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

2​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

1​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]

3​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

4​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table8"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each "a"),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"Count", each _, type table}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom.1", each List.Distinct(Table.Column([Count],"Column1"))),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Custom.1", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Removed Other Columns" = Table.SelectColumns(#"Extracted Values",{"Custom.1"})
in
    #"Removed Other Columns"[/SIZE]

btw, your last example is incorrect

For example

A1 = 1
A2 = 1
A2 = 3
A2 = 4
A2 = 5

 

[steve the fish]

Use this:

Function TJOIN(delimeter As String, ignore_blanks As Boolean, range1 As Range)

Dim arr, i As Long, myVal As String

If range1.Cells.Count = 1 Then
    TJOIN = range1.Value
    Exit Function
Else
    arr = range1
    For i = LBound(arr) To UBound(arr)
        For j = LBound(arr, 2) To UBound(arr, 2)
            If ignore_blanks = True Then
                If Len(arr(i, j)) > 0 Then
                    myVal = myVal & arr(i, j) & delimeter
                End If
            Else
                myVal = myVal & arr(i, j) & delimeter
            End If
        Next
    Next
End If

If Len(myVal) > 0 Then
    TJOIN = Left(myVal, Len(myVal) - 1)
Else
    TJOIN = ""
End If

End Function

Then you can use it on the worksheet like:

=TJOIN(",",TRUE,A1:B3)

where the first part is your delimiter ie the comma, the 2nd part is TRUE ie ignore blanks and the 3rd part is the range to join.

 

[hrayani]

Thanks Steve,

Working Perfect....

Can it ignore duplicates like the the code i am currently using (see post # 16)... The only problem is that its giving a comma at the end

Any Idea ?

 

[steve the fish]

Your one doesnt produce the comma like this:

=concatPlus(A1:A5,",",TRUE,TRUE)