WPS Office

Free All-in-One Office Suite with PDF Editor

correct-icon

Edit Word, Excel, and PPT for FREE.

correct-icon

Read, edit, and convert PDFs with the powerful PDF toolkit.

correct-icon

Microsoft-like interface, easy to use.

Free download

Windows • MacOS • Linux • iOS • Android

banner

How to Add Comma in Excel Column Between Names

July 21, 2022
29.7K Views

Excel, a powerful tool for data manipulation and analysis, holds immense potential. Yet, for those new to its vast capabilities, simple tasks like adding commas to values can pose a challenge. Many users find themselves grappling with this issue, especially when confronted with large datasets. How can one seamlessly add commas to numeric values in an Excel column? How can names and numbers be adorned with commas effortlessly? Fear not, for within this article lies the solution. In this section, we will explore multiple methods to accomplish this task seamlessly.

How to add commas in Excel?

Excel uses the comma style to separate different lengths of numbers, such as hundreds, thousands, millions, etc. Users are able to read and spell the numbers incorrectly because to this. 

Method 1: Using the Comma Style Format

Select Format Cells from the right-click menu, then check the option next to Use 1000 separator in the Number section to enable the comma in any cell (,). In the number part of the Home menu ribbons, we may also utilise the comma style. To apply comma style, we can also utilise shortcut keys by hitting ALT + H + K at the same time. The Home tab's Number format area is where you'll find the comma style format.

The thousands separator is another name for the comma style format.

When working on a large table of financial sales data (quarterly, half-yearly, or annual sales data), this format will be quite useful since it applies a comma-style structure, which makes the figures seem nicer. It is used to describe how individual digits behave in respect to groups of thousands, millions, or billions of other digits. A decent substitute for the currency format is the comma style format. To separate thousands, hundred thousand, millions, billions, and other huge figures, the comma style format adds commas. A sort of number format known as comma style adds commas to huge numbers, rounds decimal digits up to two places (so that 1000 becomes 1,000.00), shows negative values in closed parenthesis, and denotes zeros with a dash (-).

Note:ALT + H+K is the shortcut key for comma style.

Checking if decimal & thousand separators are enabled or not in Excel is necessary before working on comma style number format. If it is not enabled, we must activate it and update it using the procedures listed below.

1.Click the File tab in Excel to verify decimal and thousands of separators.

2. The Excel Options dialogue box opens when you select Options from the list of items on the left.

3. Excel Options dialogue box. In the list of options on the left, select Advanced.

4. If the Use system separators checkbox is not already checked, choose it by clicking or ticking it under the editing options. Once you choose the Use system separators checkbox, the Decimal separator and Thousands separator edit boxes appear. The thousands separator in this case was a comma, and the decimal separator was a full point. Click OK after that.

5. When you utilise the comma style number format, these separators are automatically added to all the numbers in your worksheet.

Method 2: Utilizing Custom Number Formatting

custom number excel

Excel provides the flexibility to create custom number formats to suit your specific needs. To add commas using custom number formatting, follow these steps:

  1. Select the cells or column you want to format.

  2. Right-click and choose "Format Cells" from the context menu.

  3. In the "Format Cells" dialog box, go to the "Number" tab.

  4. Select the "Custom" category.

  5. In the "Type" field, enter the custom format: #,##0.

  6. Click "OK" to apply the formatting.

Using custom number formatting allows you to add commas while retaining control over other formatting aspects such as decimal places and currency symbols.

How to add commas in excel between names?

Adding commas between names in Excel is a useful formatting technique when dealing with lists or datasets containing multiple names. By inserting commas, you can enhance readability and organize the names in a clear and structured manner. Here's a step-by-step guide on how to add commas between names in Excel:

  1. Assuming the names are in a single column (e.g., Column A), create a new column adjacent to it (e.g., Column B) to contain the modified names.

  2. In the first cell of Column B (B1), enter the formula: =A1&", ".

  3. Press Enter to apply the formula.

  4. Drag the fill handle of the cell (B1) down to copy the formula to the rest of the cells in Column B.

  5. The names in Column B will now have commas inserted between them.

  6. This approach concatenates the name in Column A with a comma and a space (", ") in Column B. By copying the formula to the remaining cells, all names will have commas inserted between them.

How to add commas in excel between numbers ?

Adding commas between numbers in Excel is a common formatting requirement, especially when dealing with large numerical datasets. This formatting technique enhances data readability and makes numbers easier to comprehend. Follow these steps to add commas between numbers in Excel:

  1. Select the cells or column containing the numbers you want to format.

  2. Right-click and choose "Format Cells" from the context menu.

  3. In the "Format Cells" dialog box, go to the "Number" tab.

  4. Select the "Number" category.

  5. Choose the desired number of decimal places or select "0" for whole numbers.

  6. Check the "Use 1000 Separator (,)" box.

  7. Click "OK" to apply the formatting.

By applying these formatting settings, Excel will automatically insert commas in the selected range, ensuring that numbers are displayed with the appropriate comma separators.

How to use a formula to add a comma at the end of a cell/text ?

In Excel, you can use a formula to add a comma at the end of a cell or text value. This technique is useful when you need to modify or concatenate data while ensuring consistency and proper formatting. Follow these steps to add a comma at the end of a cell or text using a formula:

  • Assuming the cell or text value is in cell A1, create a new cell (e.g., B1) where you want the modified value to appear.

  • In cell B1, enter the formula: =A1&",".

  • Press Enter to apply the formula.

  • The formula concatenates the value in cell A1 with a comma (",") at the end, resulting in the modified value in cell B1. If you want to add a space after the comma, modify the formula to =A1&", ".

You can then copy the formula in cell B1 to other cells by dragging the fill handle or using the copy-paste method. This way, you can quickly apply the formula and add commas at the end of multiple cells or text values.

How to add commas using the CONCATENATE function?

concatenate function excel

The CONCATENATE function in Excel allows you to combine multiple text values or cell references into a single cell. By leveraging this function, you can add commas to your desired location within the concatenated text. Follow these steps to add commas using the CONCATENATE function:

  • Determine the cells or text values you want to combine with commas.

  • In a new cell, enter the CONCATENATE function. The syntax is: =CONCATENATE(text1, ",", text2, ",", ...), where "text1", "text2", and so on represent the cells or text values you want to concatenate.

  • Replace "text1", "text2", and so on with the actual cell references or text values. Remember to place a comma (",") between each text value to insert the commas.

  • Press Enter to apply the CONCATENATE function.

For example, if you want to combine the values from cell A1, cell B1, and cell C1 with commas, the formula would be: =CONCATENATE(A1, ",", B1, ",", C1).

The CONCATENATE function will join the specified text values or cell references together and insert commas between them. You can adjust the formula as needed, including adding additional text values or cells and adjusting the comma placement.

How to apply comma in excel

In the example shown below, I have monthly sales data from a firm that includes retail, online, and vendor sales statistics on a monthly basis. In this raw sales data, there is no numerical format added to the data's presentation.

Therefore, for these positive sales amounts, I must use the comma style number format.

1.To show numbers in a comma style number format, select the cells that contain numeric sales data.

2. You may choose the comma symbol and click the Comma Style command in the Number group on the Home tab.

3. When you select the Comma Style option, Excel separates the thousands with a comma and adds two decimal places at the end. This alters the sales amount.

4. You can see that 603889 becomes 6,03,889 in the first cell. The outcome is displayed below.

Note: This was an attempt to show you how to add comma in excel online, 2016 and 2019, in both windows and mac. 

Part 2. The best office software WPS office

When it comes to office software, WPS Office stands out as a top contender. With its extensive range of features and user-friendly interface, it has become a popular choice among users worldwide. In this section, we will explore the key highlights of WPS Office, emphasizing its unique advantages and capabilities.

  • Free Editing of Word, Excel, and PPT: One of the standout features of WPS Office is that it offers free editing capabilities for Word documents, Excel spreadsheets, and PowerPoint presentations. Unlike some other office suites that require paid subscriptions, WPS Office allows users to create, edit, and format their documents without any cost.

  • PDF Functionality and Conversion: Apart from its proficiency in handling Word, Excel, and PowerPoint files, WPS Office also boasts powerful PDF functionality. Upon downloading and installing WPS Office for the first time, users can obtain a membership that unlocks the ability to convert various PDF documents.

  • Streamlined and User-Friendly Interface: WPS Office prides itself on its intuitive and user-friendly interface, designed to enhance productivity and simplify the document creation process. The software ensures a smooth and familiar experience, making it easy for users to navigate and utilize its wide range of features.

  • Cross-Platform Compatibility: Another significant advantage of WPS Office is its cross-platform compatibility. It supports various operating systems, including Windows, macOS, Linux, iOS, and Android. This means that users can seamlessly access and edit their documents from different devices, allowing for greater flexibility and convenience.

FAQs About add comma in excel

Q1: How do I add commas to text in Excel?

A1: To add commas to text in Excel, you can use the CONCATENATE function or the ampersand (&) symbol to combine the text with a comma. For example, if the text is in cell A1, you can use the formula "=A1 & ","" to add a comma at the end of the text.

Q2: How do you put commas in a list of 3?

A2: To put commas in a list of three items, you can simply separate each item with a comma. For example: "item1, item2, item3".

Q3: Do you add a comma before "and" in a list?

A3: The use of a comma before "and" in a list, also known as the Oxford comma, is a matter of style and personal preference. Some style guides recommend using the Oxford comma, while others do not. It is advisable to follow the guidelines provided by your organization or adhere to the preferred style guide for consistency in your writing.

WPS Office: Use Word, Excel, and PPT for FREE, No Ads.

Final Thought

In conclusion, mastering the art of adding commas in Excel columns is a valuable skill that can greatly enhance your data formatting and presentation. Throughout this article, we have explored multiple scenario methods, including using the TEXT function, employing the CONCATENATE function, and utilizing formulas to add commas at the end of cells or text. By following these techniques, you can effortlessly achieve the desired comma formatting in your Excel columns, improving clarity and readability.

Additionally, it's worth noting that office software solutions like WPS Office can further simplify your data manipulation tasks. With its comprehensive suite of tools and user-friendly interface, WPS Office provides a seamless experience for editing, formatting, and managing your Excel spreadsheets. Moreover, the availability of WPS templates allows you to leverage pre-designed layouts and formats, including those with pre-configured comma settings, saving you time and effort in your data formatting endeavors.

By incorporating the methods discussed in this article and leveraging the capabilities of WPS Office, you can optimize your Excel columns, present data more effectively, and streamline your workflow. Enhancing your proficiency in adding commas in Excel will undoubtedly contribute to your data management prowess, enabling you to present information in a clear and professional manner.

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.