A question about merging a name list within a word container.

[Nick Kaufman]

Sorry if I am not using the proper terminology. I think I have a pretty simple problem to fix, but I don't know how to solve it.

Let's say I have a list of hundreds of names in excel. And then I have a container like AND " ". What I want is to find some code that allows me to place each name in the word container and create a plain txt file looking like this:

AND "NAME1" AND "NAME2" AND "NAME3" AND "NAME4" etc.

How do I do this?

PS. When googling, I find a lot of info about mail merge, but I don't think it's the same, because this creates personalized individual letters.

 

[swiftaw]

See attached file, you would then copy and paste the text shown in H5 into a blank text file. This will work for any number of names in column A. Also, you can change the wrapper by changing the entries in H1 I1 and J1.

 

[Max Power]

If you saved the Excel as a CSV, you could do this in Powershell very easily. Import the CSV into a variable, then spit that out in the format you want using a foreach loop.

 

[RIFan]

If you have all the names in column A for example, you could create a simple formula in Column B to join them. Assume 1st name is in A1, starting in B2
B2 =A1&" AND "&B1
B3 = B2&" AND "A3 Copy that formula to the last name. You can then text copy the final cell contents to a notebook or word file.

 

[Nick Kaufman]

@swiftaw @Max Power @RIFan

Thank you very much, much obliged!

See attached file, you would then copy and paste the text shown in H5 into a blank text file. This will work for any number of names in column A. Also, you can change the wrapper by changing the entries in H1 I1 and J1.

This one works the best, however, it can only do a list with 7 names. I 've tried a few things, but for some reason, it gives me a #VALUE! error after the 8th row. Any ideas?

 

[steeplechase3k]

If you saved the Excel as a CSV, you could do this in Powershell very easily. Import the CSV into a variable, then spit that out in the format you want using a foreach loop.

I might be misunderstanding, but if you create the CSV, can't you just open in a good text editor like notepad++, then use Find And Replace to replace the line break with whatever text you want between the names?

I do something similar to move data from Excel to sql queries in my job and it's pretty easy.

 

[swiftaw]

@swiftaw @Max Power @RIFan

Thank you very much, much obliged!



This one works the best, however, it can only do a list with 7 names. I 've tried a few things, but for some reason, it gives me a #VALUE! error after the 8th row. Any ideas?

@swiftaw @Max Power @RIFan

Thank you very much, much obliged!



This one works the best, however, it can only do a list with 7 names. I 've tried a few things, but for some reason, it gives me a #VALUE! error after the 8th row. Any ideas?

Try it now

 

[Max Power]

I might be misunderstanding, but if you create the CSV, can't you just open in a good text editor like notepad++, then use Find And Replace to replace the line break with whatever text you want between the names?

I do something similar to move data from Excel to sql queries in my job and it's pretty easy.

With one column, yes. But if you had multiple columns that needed to be separate files, a short script makes it a lot easier.

 

[steeplechase3k]

With one column, yes. But if you had multiple columns that needed to be separate files, a short script makes it a lot easier.

Ah, I had missed that it was multiple columns, I never need that.

 

[Five Cent Head]

You can also use the "TEXTJOIN" function. If your data is in columns A and B, put something like this in a convenient cell:

Using single-quotes instead of double-quotes (to produce AND 'x' AND 'y' AND 'z' AND ...):

="AND '" & TEXTJOIN("' AND '", TRUE, A1:A200, B1:B200) & "'"

Explanation: AND concatenated (that's what & does) with "'" (that's a single quote inside two double quotes) first, then join all of the names with ' AND ' in between names. End with another '.

Now using double-quotes instead: replace "'" with CHAR(34):

="AND " & CHAR(34) & TEXTJOIN(CHAR(34) & " AND " & CHAR(34), TRUE, A1:A200, B1:B200) & CHAR(34)


(You should edit A1:A200 and B1:B200 to reflect where your data actually is.)