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:
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:
<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>
[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:
Hope this helps! Respond here if you have further questions or comments.
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:
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: