SOLVED

User Input Outcome

Occasional Contributor

Dear All

I hope you are having a great day 

 

first, I would like to thank you all for your effort 

 

I need your support regarding this issue: 

 

  • Device and OS platform, Windows 10 
  • Excel product name and version number 2013
  • Excel file you’re working with: sample file 

Untitled.png

 

as attached above, I want the result to be like the last column. 

is there any code or macros doing this? 

 

 

thank you again for your kind help ... 

 

 

4 Replies
best response confirmed by style3body (Occasional Contributor)
Solution

@style3body 

Create the following function in a module in the Visual Basic Editor:

Function Res(rng As Range) As String
    Dim i As Long
    Dim s As String
    For i = 1 To Application.CountA(rng) - 1
        s = s & "," & rng(i).Value
    Next i
    If s <> "" Then
        Res = Mid(s, 2)
    End If
End Function

Let's say the first input range is A2:D2. In E2, enter the formula

=Res(A2:D2)

This can be filled/copied down.

S0583.png

@Hans Vogelaar 

 

Thank you so much, it works really well.

 

 

@Hans Vogelaar 

 

Hello Again...  

Kindly, What if I need like the following : 

 

can you do that if possible?

 

 

Example 1Example 1

 

 

and also : 

 

Example 2Example 2

 

Thank you in advance.. 

@style3body 

To specify a separator, use this function:

Function Result2(rng As Range, sep As String) As String
    Dim cel As Range
    Dim s As String
    For Each cel In rng
        If cel.Value <> "" Then
            s = s & sep & cel.Value
        End If
    Next cel
    If s <> "" Then
        Result2 = Mid(s, Len(sep) + 1)
    End If
End Function

Use like this:

=Result2(A2:D2,"-")

 

About your second request: do you want to specify the number of characters for each row? Or is there some kind of "rule" that determines the number of characters?