Combining 4 letters from column A and 4 letters from column B

[Eric W]

I'm opening this thread on behalf of @Danya Subramanian who sent me a PM:

Take the first 4 letters from a given word in column A and last four letters from a given word in column B and concat them in column C using VBA.

First, Danya, if you see this, it's preferred that you open a new thread if you have a new question. That way others can benefit from seeing the answer. Also, you can't receive PMs until you have a certain number of posts, so I can't send you an answer that way anyway.

Next, I think your question is a little vague. Are you talking about 2 particular words, or do you want a list of all the possible combinations?

Consider:

	A	B	C	D	E
1	aaaa1234	yyyy1111	aaaa1111
2	bbbb4567	zzzz2222	aaaa2222
3	cccc9999		bbbb1111	aaaa2222
4			bbbb2222
5			cccc1111
6			cccc2222

<tbody>
</tbody>

Sheet15​

Worksheet Formulas Cell Formula D3 =CONCATds(A1,B2) <tbody> </tbody>

<tbody>
</tbody>

If you want 2 particular words, the formula is in D3. If you want the whole list in C, then you can use a macro. The code for both is here:

Function ConcatDS(ByVal targ1 As String, ByVal targ2 As String)

    ConcatDS = Left(targ1, 4) & Right(targ2, 4)
End Function

Sub MultipleConcatDS()
Dim SD As Object, list1 As Variant, list2 As Variant, r1 As Long, r2 As Long

    Set SD = CreateObject("Scripting.Dictionary")
    list1 = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Value
    list2 = Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row).Value
    
    For r1 = 1 To UBound(list1)
        For r2 = 1 To UBound(list2)
            SD(Left(list1(r1, 1), 4) & Right(list2(r2, 1), 4)) = 1
        Next r2
    Next r1
    Range("C1").Resize(SD.Count) = WorksheetFunction.Transpose(SD.keys)
            
End Sub

Hope this helps! Respond here if you have further questions or comments.

 

[Joe4]

First, Danya, if you see this, it's preferred that you open a new thread if you have a new question. That way others can benefit from seeing the answer. Also, you can't receive PMs until you have a certain number of posts, so I can't send you an answer that way anyway.

Its not only preferred, its actually a rule.
See rule 4 here: https://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html