SOLVED

# User Input Outcome

Occasional Contributor

# User Input Outcome

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

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

# Re: User Input Outcome

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.

# Re: User Input Outcome

Thank you so much, it works really well.

# Re: User Input Outcome

Hello Again...

Kindly, What if I need like the following :

can you do that if possible?

Example 1

and also :

Example 2

# Re: User Input Outcome

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?