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

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
13,310
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:

ABCDE
aaaa1234yyyy1111aaaa1111
bbbb4567zzzz2222aaaa2222
cccc9999bbbb1111aaaa2222
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:

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

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

Code:
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.
 
Last edited:

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top