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
aaaa1234	yyyy1111	aaaa1111
bbbb4567	zzzz2222	aaaa2222
cccc9999		bbbb1111	aaaa2222
bbbb2222
cccc1111
cccc2222

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet15​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]D3[/TH]
[TD="align: left"]=CONCATds(A1,B2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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