Remove characters after the last digit

Question

The code I am using is formatting numbers from the column into a string with numbers separated by a comma. Works well up to a point. E.g. My range that I "concatenate" into a comma delimited string has 398 possible cells (i.e. A2:A400), if e.g. the data starts in A300:A400 I get a perfect string, but if it goes from e.g. A300:A370, then I get 30 commas (,) after the last number. Example:

A300:A400 = ...-0.12345,0.34232,0.221312,0.231132

A300:A370 = ...-0.3345,0.014332,0.0021,-0.120031,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

So, the code picks up blank files between A370 and A400 and adds commas. I don't want any commas after the last digit, as it is the case with the range A300-A400. Please advise. Thank you

Sub Coltocommadelimitstring()
Dim rng As Range
Dim InputRng As Range, OutRng As Range

Set InputRng = ThisWorkbook.Sheets(1).Range("A2:A400")
Set OutRng = ThisWorkbook.Sheets(1).Range("D2")

outStr = ""
For Each rng In InputRng
If outStr = "" Then
outStr = rng.Value
Else
outStr = outStr & "," & rng.Value
End If
Next
OutRng.Value = outStr
End Sub

put another if in to test whether `rng.Value = ""` before adding it to the string. — Scott Craner, Dec 02 '19 at 14:18
Depending on your version of excel this can be done with TEXTJOIN(). — Scott Craner, Dec 02 '19 at 14:19
Here is a UDF that mimics TEXTJOIN: https://stackoverflow.com/questions/39532189/vlookup-with-multiple-criteria-returning-values-in-one-cell — Scott Craner, Dec 02 '19 at 14:50

Xabier · Accepted Answer · 2019-12-02T14:59:36.490

The following formula would get you your desired outcome, simply enter it on cell D2:

=TEXTJOIN(",",TRUE,A2:A400)

Textjoin works like concatenate but can have a delimiter as an argument, also it gives you the ability to ignore blank cells, the first argument is the delimiter, the second is the flag to ignore blanks and the third is for the range.

TEXTJOIN is only available for Office 365 subscribers, a possible alternative would be to build your UDF as below, this will allow you to use the formula above without an Office 365 subscription:

Function TEXTJOIN(delimiter As String, ignore_empty As Boolean, rng As Range) As String
Dim compiled As String
For Each cell In rng
    If ignore_empty And IsEmpty(cell.Value) Then
        'nothing
    Else
        compiled = compiled + IIf(compiled = "", "", delimiter) + CStr(cell.Value)
    End If
Next
TEXTJOIN = compiled
End Function

As an alternative to TEXTJOIN, as the comments suggested, you can add another check to see if rng.Value = "" before appending it to the string, as below:

Sub Coltocommadelimitstring()
Dim rng As Range
Dim InputRng As Range, OutRng As Range

Set InputRng = ThisWorkbook.Sheets(1).Range("A2:A400")
Set OutRng = ThisWorkbook.Sheets(1).Range("D2")

outStr = ""
For Each rng In InputRng
    If outStr = "" Then
        outStr = rng.Value
    Else
        If rng.Value <> "" Then outStr = outStr & "," & rng.Value
    End If
Next
OutRng.Value = outStr
End Sub

Perfect! Thank you :) – Alien_Explorer Dec 02 '19 at 15:06 — Alien_Explorer, Dec 02 '19 at 15:06

Remove characters after the last digit

1 Answers1