Eric W
MrExcel MVP
- Joined
- Aug 18, 2015
- Messages
- 12,981
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>
</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:
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 | |
---|---|---|---|---|---|
1 | aaaa1234 | yyyy1111 | aaaa1111 | ||
2 | bbbb4567 | zzzz2222 | aaaa2222 | ||
3 | cccc9999 | bbbb1111 | aaaa2222 | ||
4 | bbbb2222 | ||||
5 | cccc1111 | ||||
6 | cccc2222 |
<tbody>
</tbody>
Sheet15
Worksheet Formulas
<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:
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: