Excel Tricks: Build SQL Queries, C# Lists, Detect Duplicates, Split Cells, Remove Hyperlinks

1. Concatenating SQL Strings

When a large number of values need to be queried from a database, copying and pasting each value is impractical. By using Excel you can concatenate a column of values into an IN clause for SQL or into a C# List initializer.

Example Excel data (store codes) is shown below:

The following animated screenshots demonstrate the steps to create the concatenated string, copy it, and paste it into Visual Studio Code for replacement.

For C# list generation, the same approach is used; the resulting list can be pasted directly into code.

2. Finding Duplicate Entries

Excel’s conditional formatting can highlight duplicate values in a column, making it easy to identify and clean redundant data.

Steps:

Select the column (e.g., "Store Code").

Navigate to Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values .

Accept the default formatting to see duplicates highlighted.

Optionally filter by the highlight color via Data → Filter to view only duplicates.

To remove the rule, use Home → Conditional Formatting → Clear Rules → Clear Rules from Entire Sheet .

Illustrations:

3. Splitting Cell Content into Multiple Columns

When logs or combined strings (e.g., "1274206,商品1274206已淘汰") need to be broken into separate fields for reporting, Excel’s Text‑to‑Columns feature can split them by a delimiter such as a comma.

Steps:

Select the cells to split and choose Data → Text to Columns .

Choose Delimited and click Next .

Select the comma (,) as the delimiter, then finish.

Resulting split view:

4. Permanently Disabling Automatic Hyperlinks

Excel automatically converts URLs into clickable hyperlinks, which can cause accidental clicks. To turn this off permanently (Excel 2013 and later):

Open File → Options and select the Proofing tab.

Click AutoCorrect Options .

Uncheck Internet and network paths with hyperlinks .

After confirming, Excel will no longer auto‑create hyperlinks.

These Excel techniques enable developers to handle bulk data transformations, generate code snippets, clean duplicate records, and avoid unwanted hyperlink behavior, thereby streamlining routine development and data‑processing tasks.