Late last year I took over a product code migration for a colleague. He had a 120-line list exported from the admin panel, one code per line, shaped like SKU-10086. He needed it folded into an SQL IN clause, with every line wrapped in quotes and followed by a comma. I thought this was a case for hand editing. Then I missed a comma around line forty, an extra space appeared at the end of a line in the sixties, the pasted block failed outright, and finding the problem took several times longer than the estimate. Since then, any job where every line gets the same treatment no longer gets typed out line by line.
Hand edits hide errors at line ends and on the last line
The hard part of quoting every line is not typing but making every line identical. Two strings look the same to the eye while one ends with a space and the other does not, and in SQL those are two different values. I counted three kinds of error that time: a missing comma, an extra space, and the most invisible one, the last line. The first 119 lines should all be "SKU-10086",, while the last should have no trailing comma, and most databases treat IN ('a','b',) as a syntax error. The rule itself is all lines the same except the last, which is exactly what people are worst at.
I also tried find and replace. Rewriting each line as "$1", with a backreference was the right idea, but the file was exported on Windows, where a line ends with a carriage return plus a newline. My pattern matched only the newline, so every line kept an invisible carriage return at the end and the query editor still errored.
Prefix and suffix: turn each line into a literal
What this list needs is simply one character added on the left of each line and two on the right. Using the prefix and suffix mode in Line Operations, I set the prefix to a single ASCII double quote " and the suffix to ", (a quote then a comma), and all 120 lines come out at once in the form "SKU-10086",, ready to paste inside the parentheses. The catch is the order of the two characters in the suffix: write "," and the comma comes first, which is a guaranteed syntax error.
Switching languages just means switching quote style. In a JavaScript array I prefer single quotes, so the prefix is ' and the suffix is ',. If values may contain single quotes, decide which quote to wrap with beforehand instead of hunting conflicts by hand afterwards.
"SKU-10086",
"SKU-10087",
"SKU-20011",Numbers are not just stamped on: set the start value and step in advance
The other common task is numbering. Adding 1. 2. to a table of contents or a list of questions seems trivial, but two decisions shape everything after. First, the start value: one batch of my lists continued from the previous month, which had ended at 100, so this batch had to start at 101 rather than pad in 100 blank lines. Second, the step: stepping by 5 or 10 leaves room to insert items later without renumbering, while stepping by 1 is the most natural, until you need something between items 3 and 4 and end up with a number like 3.5.
Zero padding is something I only started taking seriously after it bit me. Sorted as plain strings, 10. comes before 2., because the first character is what gets compared. Once a list approaches or passes 10 items, I pad to 01. 02. so all numbers are the same length, and sorting or filename use will not scramble them later.
Deleting and slicing: count the position first, then check that line lengths hold
I also use the reverse of prefixing. In that batch, SKU- was a fixed 4 characters, so when I needed bare numbers I chose "delete the first 4 characters of each line", and when I wanted only the leading digits I used a slice. Before using either, I scan the shortest line: deletion and slicing go by position, and a line shorter than n characters cannot give you the full target, only a fragment or an empty line. So I compare the line count again; if it differs from the input, some line was emptied.
The table below is how I worked backwards from several target shapes at the time; every character is safe to copy as written, so you can try it as is.
| Target shape | Prefix | Suffix | Numbering | Input example | Output example |
|---|---|---|---|---|---|
| SQL IN clause | " | ", | Off | SKU-10086 | "SKU-10086", |
| JS array element (single quotes) | ' | ', | Off | SKU-10086 | 'SKU-10086', |
| Numbered list | Leave empty | Leave empty | Start 1, step 1, pad to two digits | apple | 01. apple |
| Continuing last month's numbering | Leave empty | Leave empty | Start 101, step 5 | apple | 101. apple |
| Strip a fixed prefix | — | — | Delete the first 4 characters | SKU-10086 | 10086 |
I use spreadsheet formulas too, but not everywhere
If the data already lives in a spreadsheet and other columns need calculating, a formula is handier. In Excel, to add quotes and a comma to A1, I write =CHAR(34)&A1&CHAR(34)&",", where 34 is the decimal code point of a double quote; that reads better than escaping four double quotes in a row and is harder to miscount. Fill down 120 rows, copy, then paste back as values only, and it is not slow either.
But I now choose formulas in only two cases: the column itself takes part in a calculation, or the file is already open in a spreadsheet. For a plain text list I avoid that route: pasting back requires one more paste-as-values step, and the spreadsheet outsmarts you by changing content. A 12-digit numeric string becomes scientific notation, precision beyond 15 digits is dropped outright, and a code that looks like a date, such as 2024120310001, may be read as a number. When a list first needs cleaning up, I run Text Sort and Dedupe to remove duplicates before adding prefixes or suffixes; do it in the other order and the duplicates become duplicate quoted lines, looking like a data problem.
When the task is editing the middle of a line, I switch tools
Prefixes, suffixes, numbering, deletion by position and slicing cover the case where every line gets the same action. Once the insertion point differs from line to line, for example turning name=Zhang San into name='Zhang San', it is no longer a line-level batch operation and needs a pattern-based replacement; then I go to Batch Find and Replace, capture the value and add the quotes. Knowing whether a task follows a positional pattern across lines or a content pattern within lines is more useful than memorizing what each tool can do.
A few questions I asked myself at the time
Q: Lines vary in length; can a prefix land in the wrong place?
Prefixes, suffixes and numbering do not care about line length; they attach at the start or end of the line and treat every line the same, so I never worry about uneven lines when adding quotes and commas. What does depend on length is "delete the first n characters" and "take the first n"; for those, confirm in advance that the shortest line is long enough.
Q: Does zero padding do anything besides look neat?
Yes, mainly for sorting and filenames. Only equal-length strings sort the same as numbers; in filenames, 01.jpg through 12.jpg stay in order, while 1.jpg, 10.jpg, 2.jpg do not.
Q: After processing hundreds of lines at once, how do I check the result?
I look at two numbers: whether the output line count equals the input line count, and whether the last line or two look right. A mismatch means a line was emptied or a blank line slipped in; a wrong ending usually means the comma position or where the numbering stopped. These two checks are far more reliable than my old edit-then-paste approach.
Q: Do blank lines in the original get quotes too?
Yes, they are treated as a line and come out as a pair of quotes plus a comma, an item that appeared from nowhere. So I remove blank lines first, then do the line-level work, and drop duplicates in that same pass.
Q: What is the safest way to handle the trailing comma on the last line?
I prefer to ignore it: generate every line with a comma, paste the block into the parentheses, then delete the last one. The real danger is relying on a person to remember that the last line is different; miss it once in a hundred lines and you are back to hunting for ages.
Back to that 120-line list. My rule now is simple: if the action is the same for every line and the pattern can be described by position, such as start of line, end of line, sequence number or the nth character, hand it to a line-level batch operation. If the action depends on what is inside the line, use find and replace. If the data also needs calculating and is already in a spreadsheet, then use a formula. The lesson was not that manual editing is slow, since slowness is only the symptom; the real waste is rules like the last line is the exception, which a machine never misses and a person always will.