How to Remove Comma Between Numbers in Excel

Get FREE Advanced Excel Exercises with Solutions!

Commas have several uses while dealing with spreadsheets in Excel. For example, they improve the readability of numbers. However, there may be situations when you need to remove the unnecessary commas between numbers from the worksheet. This tutorial will guide you with some quick approaches to remove the comma between numbers in Excel.

In this article, we will demonstrate five quick methods to remove commas between numbers in Excel. To explain the methods we have used some excellent examples with explanations. By adopting these approaches, you will be able to remove commas between numbers very easily even if the dataset is huge. So, without further delay, let’s get started.


1. Use Format Cells Option to Remove Comma Between Numbers in Excel

Suppose, we have a dataset (B4:D9) in Excel that contains the Years and their corresponding Sales values with commas. Now, we need to remove the commas between the numbers of Sales (C5:C9). Here, we will use the Format Cells dialog box for removing the commas. The steps to do so are below.

Use Format Cells Dialog Box to Remove Comma Between Numbers in Excel

Steps:

  • First of all, copy the values of Sales (C5:C9) and paste them into D5:D9.

  • Secondly, select the range D5:D9.
  • Thirdly, go to the Home tab.
  • Fourthly, click on the dialog box launcher (Number Format) from the Number group (see screenshot).

  • In turn, the Format Cells dialog box will pop up.
  • Therefore, go to the Number tab > Category > Number > uncheck the Use 1000 Separator (,) box > OK.

Use Format Cells Dialog Box to Remove Comma Between Numbers in Excel

  • In this way, we can remove the commas between the numbers easily (see screenshot).


2. Delete Comma Between Numbers with Excel NUMBERVALUE Function

In the second approach, we will use the NUMBERVALUE function in Excel to omit the commas between numbers. For this, we will use the dataset (B4:D9) below containing some Sales values with commas (C5:C9). Follow the steps below to remove the commas using the NUMBERVALUE function.

Omit Comma Between Numbers with Excel NUMBERVALUE Function

Steps:

  • First, select cell D5.
  • Next, to remove the comma in the cell C5, type the following formula in cell D5:
=NUMBERVALUE(C5)

  • After that, press the Enter key.
  • Thus, we have removed the comma.
  • See the number without comma in cell D5 of the picture below.

Omit Comma Between Numbers with Excel NUMBERVALUE Function

  • Besides, to copy the formula in the rest of the cells (D5:D9), double-click on the fill handle.
  • In this way, we can remove all the commas from the numbers (D5:D9).

Read More: How to Remove Comma in Excel Using Formula (2 Ways)


3. Discard Thousands Comma Separators from Numbers by Applying the General Format

Here, we will explain the process to discard the thousands comma separators from numbers. For this, we will use the dataset below (B4:D9) that contains Sales values with thousands comma separators in the C5:C9 range. Now, we will remove these commas by applying the General Format. The steps to do so are below.

Steps:

  • To do so, first of all, we need to copy the range C5:C9 and paste it into D5:D9.

Discard Thousands Comma Separators from Numbers Applying General Format

  • Afterward, select the range D5:D9 > go to the Home tab > Number group > click on the dropdown (see screenshot).

  • Then, select General from the dropdown.

  • Consequently, all the commas are removed from the numbers (D5:D9).

Read More: How to Change Comma Separator in Excel (7 Easy Methods)


4. Converting Comma into Decimal Points in Excel Using the Find and Replace Option

Assuming, we have a dataset (B4:D9) in Excel containing the Years (B5:C9) and their respective Sales (C5:C9) values. We can see that the numbers of Sales (C5:C9) contain commas (see screenshot). In this method, we will utilize the Excel Find & Replace option to convert these commas into decimal points. The steps are below.

Steps:

  • In the beginning, copy C5:C9 and paste it into D5:D9.

Convert Comma into Decimal Point in Excel Using Find and Replace Option

  • After that, select D5:D9.
  • Then, go to the Home tab.

  • Thereupon, go to the Editing group > click on the Find & Select dropdown > select Replace.

  • In turn, the Find and Replace dialog box will appear.
  • At this time, go to the Replace tab > type ‘,’ in the Find what box > type ‘.’ in the Replace with box > click on Replace All.

  • Consequently, the Microsoft Excel message box appears.
  • The message box shows the total number of replacements (5 in our case).
  • Click OK.

  • Again, go to the Find and Replace dialog box.
  • Eventually, click on the Close button.

Convert Comma into Decimal Point in Excel Using Find and Replace Option

  • In the end, we can see the commas replaced with decimal points (.) in the range D5:D9.


5. Applying Text to Columns Feature to Remove Comma from Numbers in Excel

Let’s say, we have an Excel dataset ( B4:D9) that contains some Years (B4:B9) and their corresponding Sales value (C5:C9). The screenshot shows that the numbers of Sales contain commas. From the screenshot, we can also see that the Sales values are in Number format. In this method, we will learn to remove the commas from the numbers (C5:C9) using the Text to Columns feature. See the steps below.

Apply Text to Columns Feature in Excel to Remove Comma from Numbers

Steps:

  • Firstly, convert the Number format of the range C5:C9 into General format.
  • To change the formatting, select the range C5:C9 > go to the Home tab > next to the Number group > select General from the dropdown.

  • Now, keeping selection on the range C5:C9, go to the Data tab.

  • Next, click on Text to Columns in the Data Tools group.

  • As a result, the window named ‘Convert Text to Column Wizard – Step 1 of 3’ will pop up.
  • Therefore, select Delimited from the Choose the file type that best describes your data section.
  • Afterward, click on Next.

  • At this time, go to Delimiters > put a tick mark in the Comma box > see the Data preview section to see the final output of the numbers > click Next.

  • Thereupon, click on the Finish button.

  • Then, a message box named Microsoft Excel will appear.
  • It basically refers to the data of C5:C9.
  • Click OK.

Apply Text to Columns Feature in Excel to Remove Comma from Numbers

  • Finally, the parts of the numbers before and after the commas will be separated and the commas will be removed.
  • The separated portions are in the ranges C5:C9 and D5:D9 correspondingly (see screenshot).


How to Discard Comma from Text String in Excel

The previous methods were about removing commas from the numbers. Moreover, we can remove the commas between text strings in Excel. Here, we will see how to do this. To explain the process, we have used an Excel dataset (B4:C9) containing some Texts (B5:B9) with commas. We will use the SUBSTITUTE function for this method. Let’s see the steps below to remove the commas in the Texts.

How to Discard Comma from Text String in Excel

Steps:

  • In the first place, select cell C5.
  • Now, to remove the comma between the texts of cell B5, type the following formula in cell C5:
=SUBSTITUTE(B5,",","")

  • Next, press the Enter key.
  • Consequently, we can see in cell C5 that the comma between the texts of cell B5 is removed correctly.

  • However, to copy the formula in the cells below (C6:C9), double-click on the fill handle sign (the ‘+’ sign located at the corner of cell C5).

  • Thus, we can remove the commas from all the Texts of the dataset (see screenshot).

How to Discard Comma from Text String in Excel


How to Remove Numbers after Comma in Excel

Assuming, we have a dataset (B4:C9) in Excel that contains some Numbers (B5:C9). There are commas between these numbers. Now, we will demonstrate the procedure to remove the portions of numbers after commas along with commas. Here, we have used the LEFT function and the SEARCH function. See the steps below.

How to Remove Numbers after Commas in Excel

Steps:

  • First, activate cell C5.
  • Next, to remove the number after the comma in cell B5, enter the formula below in cell C5:
=LEFT(B5,SEARCH(",",B5)-1)+0

In this formula, the SEARCH function locates the comma (,) in the number in cell B5. Besides, the LEFT function returns the first three characters from the number.

  • Eventually, press the Enter key to get the result.
  • Consequently, the comma and the portion of the Number after the comma is removed (see screenshot).

  • To copy the formula in the rest of the cells, drag the fill handle up to cell C9.
  • We can see the final result in the screenshot below.

How to Remove Numbers after Commas in Excel


How to Get Rid of the Last Comma in Excel

Lastly, we will see the process to delete the last comma in Excel. Suppose, we have a dataset (B4:C9) below containing some Texts in the range B5:B9. We can see commas at the end of every Text. Now, we will use some Excel functions to delete the last commas. The functions are the IF function, the OR function, the RIGHT function, the TRIM function, the LEFT function and finally the LEN function. The steps are below.

How to Delete the Last Comma in Excel

Steps:

  • To begin, select cell C5.
  • Therefore, to remove the last comma of cell B5, enter the formula below in cell C5:
=IF(OR(RIGHT(TRIM(B5),1)={",","."}),LEFT(TRIM(B5),LEN(TRIM(B5))-1),TRIM(B5))

  • Next, press Enter.
  • In turn, we can see in cell C5 that the last comma of cell B5 is removed.

🔎 How Does the Formula Work?

  • TRIM(B5): Discard all unnecessary spaces from the text.
  • RIGHT(returned text by TRIM,1): Retrieves the trimmed text’s rightmost element.
  • OR(right, most, character, of, trimmed, text={“,”,”.”}): If the rightmost character is either a comma or a period then it is TRUE; otherwise FALSE.
  • LEFT(TRIM(B5),LEN(TRIM(B5))-1): Retrieves the entire trimmed text excluding the final character.
  • In the end, copy the formula in the rest of the cells (C6:C9), drag the fill handle up to cell C9.
  • See the final result in the screenshot below.

How to Delete the Last Comma in Excel


Download Practice Workbook

Download the practice workbook from here.


Conclusion

I hope this tutorial will be helpful for you to remove commas between numbers in Excel. Download the practice workbook and give it a try. Let us know your feedback in the comment section. Follow our website ExcelDemy to get more articles like this.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sagufta Tarannum
Sagufta Tarannum

Sagufta Tarannum, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, contributes significantly as an Excel & VBA Content Developer at ExcelDemy. Fueled by a deep interest in research and innovation, she actively engages with Excel. In her role, Sagufta not only skillfully addresses challenging issues but also demonstrates enthusiasm and expertise in gracefully navigating intricate situations, underscoring her unwavering commitment to consistently delivering exceptional content. Her interests are Advanced... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo