# Excel - multiply texts/strings/words

I have column A with domain names and column B with TLD = Top Level Domains. I need to multiply each word from column A by each word from column B and put the result in column C (connect domain name with top level domain name). Additionally I need to add a dot between word from A and word from B.

Column C should have A*B records.

How can I do that?

``````╔═════════╦═════╦═════════════╗
║ domain1 ║ org ║ domain1.org ║
╠═════════╬═════╬═════════════╣
║ domain2 ║ net ║ domain1.net ║
╠═════════╬═════╬═════════════╣
║ domain3 ║ com ║ domain1.com ║
╠═════════╬═════╬═════════════╣
║         ║     ║ domain2.org ║
╠═════════╬═════╬═════════════╣
║         ║     ║ domain2.net ║
╚═════════╩═════╩═════════════╝
...
``````
• I have a VBA solution for this once unlocked if that is what you are after. Commented Jul 2, 2022 at 3:23

If it is not possible to use a VBA code, you can achieve the same result in a formulaic way. Enter the formula below in cell C1 and copy it down as far as it is needed (simply determined by computing the number of rows in column A times the number of rows in column B; but you see it anyways once you have empty cells). You can also paste the formula into any other cell, but you need to make sure that the appropriate parts in the formula are adjusted accordingly. The formula also works for cases where you have a different number of domains and TLD.

``````=IF(ROW()-ROW(\$C\$1)+1>COUNTA(A:A)*COUNTA(B:B),"",INDEX(A:A,INT((ROW()-ROW(\$C\$1))/COUNTA(B:B)+1))&"."&INDEX(B:B,MOD(ROW()-ROW(\$C\$1),COUNTA(B:B))+1))
``````
• Excel says that this formula is incorrect. Commented Jul 2, 2022 at 10:06
• @pbies There was a minor mistake as I not changed one semicolon. The formula should work now. But you may need also to change the commas "," to semicolons ";" depending on your system.
– carl
Commented Jul 2, 2022 at 10:07
• Polish version: `=JEŻELI(WIERSZ()-WIERSZ(\$C\$1)+1>ILE.NIEPUSTYCH(A:A)*ILE.NIEPUSTYCH(B:B);"";INDEKS(A:A;ZAOKR.DO.CAŁK((WIERSZ()-WIERSZ(\$C\$1))/ILE.NIEPUSTYCH(B:B)+1))&"."&INDEKS(B:B;MOD(WIERSZ()-WIERSZ(\$C\$1);ILE.NIEPUSTYCH(B:B))+1))` Commented Jul 2, 2022 at 10:20

Try this code, first select the range of domains, then extensions then the first cell of where you want the output to go. You need to place the code in a module and run it either directly from the module or link it to a button on the sheet.

``````Option Explicit

Sub MyDomains()
Dim x As Long, y As Long, c As Long
Dim d As Range, e As Range, o As Range
On Error GoTo Err_Exit
Set d = Application.InputBox(Prompt:="Select your domain range:", Type:=8)
Set e = Application.InputBox(Prompt:="Select your extension range:", Type:=8)
Set o = Application.InputBox(Prompt:="Select first cell of your output range:", Type:=8)
For x = 1 To d.Rows.Count
For y = 1 To e.Rows.Count
o.Offset(c, 0) = d.Cells(x, 1) & "." & e.Cells(y, 1)
c = c + 1
Next y
Next x
GoTo MExit
Err_Exit:
MsgBox "There was an error, ensure you select correct ranges."
MExit:
End Sub
``````
• What options should be selected to put that code in a module? Commented Jul 2, 2022 at 9:57
• Not sure what you mean by options. If you are unfamiliar with VBA you need to enable the developer tab, then insert a module and then paste the code in a module. You may need to do some research to get there. If you cannot get it to work let me know and I will include more complete instructions tomorrow. Commented Jul 2, 2022 at 12:11