Remove first concat to empty string

Question

currently with the codes below, output would be ~ abc~ etc~ etc~. How do I remove the first ~. So it should start with abc straight away, not ~.

Function SpecialLookup(lookup_value As String, src_rng As Range, column_index As Long)

    Dim rng As Range
    Dim xResult As String
    xResult = ""
    For Each rng In src_rng
        If rng = lookup_value Then
            xResult = xResult & "~ " & rng.Offset(0, column_index - 1).Value
        End If
    Next
    SpecialLookup = xResult
End Function

@ScottCraner yeah, sadly, i do not have it. But thank you! Just saw your answer :) helps alot — Josh Ng, Apr 23 '20 at 15:18
here is code that mimics TEXTJOIN: https://stackoverflow.com/questions/39532189/vlookup-with-multiple-criteria-returning-values-in-one-cell — Scott Craner, Apr 23 '20 at 15:19
And here is a TEXTJOINIFS: https://stackoverflow.com/questions/56858571/merge-values-of-column-b-based-on-common-values-on-column-a — Scott Craner, Apr 23 '20 at 15:21
great! thanks so much, will keep this post archived; useful for future use! — Josh Ng, Apr 23 '20 at 15:25

urdearboy · Accepted Answer · 2020-04-23T15:13:24.093

4

Mid should do the trick

SpecialLookup = Mid(xResult, 3, Len(xResult))

edited Apr 23 '20 at 15:13

answered Apr 23 '20 at 15:11

urdearboy

14,439
5
28
58

6

in vba the third criterion is optional you can get the same with `SpecialLookup = Mid(xResult, 2)` and I believe it should `SpecialLookup = Mid(xResult, 3)` as the prefix is `'~ '` – Scott Craner Apr 23 '20 at 15:13
@ScottCraner good catch on the leading space. I did not know VBA defaults to full length of string. Thanks for sharing! – urdearboy Apr 23 '20 at 15:13
Hey urdeaboy and scott craner! Thanks so much to both! works perfectly. – Josh Ng Apr 23 '20 at 15:15

score 3 · Answer 2 · answered Apr 23 '20 at 15:28

I like this pattern:

Function SpecialLookup(lookup_value As String, src_rng As Range, column_index As Long)
    Dim rng As Range
    Dim xResult As String, sep as String
    For Each rng In src_rng
        If rng = lookup_value Then
            xResult = xResult & sep & rng.Offset(0, column_index - 1).Value
            sep = "~ " '<<<<<
        End If
    Next
    SpecialLookup = xResult
End Function

score 2 · Answer 3 · answered Apr 23 '20 at 15:16

another option is an IF:

Function SpecialLookup(lookup_value As String, src_rng As Range, column_index As Long)

    Dim rng As Range
    Dim xResult As String
    xResult = ""
    For Each rng In src_rng
        If rng = lookup_value Then
            If xResult = "" Then
                xResult = rng.Offset(0, column_index - 1).Value
            Else
                xResult = xResult & "~ " & rng.Offset(0, column_index - 1).Value
            End If
        End If
    Next
    SpecialLookup = xResult
End Function

Remove first concat to empty string

3 Answers3