Open In App

How to Separate Numbers and Percentages from One Cell in Excel?

Improve
Improve
Like Article
Like
Save
Share
Report

While dealing with numbered data, we generally come across a variety of problems that hamper our work. One such problem of separating numbers and percentages from one cell. In this article, we will learn how to separate numbers and percentages from one cell.

Data Set 

The data on which to be performing the operation is as below. The data set comprises of name, salary and percentage, salary, and percentage, where the salary and percentage column contain both salary and percentage, in different formats. Our task is to separate salary and percentage into two different columns. 

given data set of salary and percentage

 

In column F, there are 3 cases in which the salary(numbers) and the percentage is written. In the first case, i.e row 3 and 4 has numbers and percentage are separated by a dash, while in case two, i.e. row 5 and 6 the number and percentage are separated by a space between them but the percentage is written in front of the number and in the third case i.e in row 7 and 8 the number and percentage are separated by a space between them.

NOTE: The operations are performed in Google sheets, but they will hold true from MS Excel 2007 and beyond. Read the article to learn about the functions used in advance.

CASE 1: Sepearting Row 3 and 4, Number and Percentage 

The first case explains, separating number and percentage, with, the percentage written in right and the number written on left, and they are separated by a hyphen(-).

Step 1: In column G and row 3 i.e cell G3 write the formula =LEFT(F3, FIND(“-“, F3)-1), as shown below, and press Enter. The formula simply extracts the index of the hyphen(-) first, and then returns the left substring, just before the hyphen(-) index. You’ll see the number is being separated from the percentage.

extracting number from hyphen(-) format

 

Step 2: In the column H and row 3 i.e cell H3 write the formula =MID(F3,FIND(“(“,F3)+1,FIND(“)”,F3)-FIND(“(“,F3)-1), as shown below, and press Enter. The formula simply extracts the next index to the “(“, and also the index of the “%”, then we use =MID() function, to provide the substring, between these indexes. 

extracting percentage from hyphen(-) format

 

Step 3: Similar, steps can be applied to Row 4. The final output looks as shown below:

output after separating, the number and percentage in case 1

 

CASE 2: Sepearting Row 5 and 6, Number and Percentage 

The second case explains, separating number and percentage, with, the percentage written on the left and the number is written on the right, separated by a space. The only change in the formula of case1 and case2 is to change the function from =LEFT() to =RIGHT() for separating the number.

Step 1: In column G and row 5 i.e cell G5 write the formula =RIGHT(F5, FIND(” “, F5)-1), as shown below, and press Enter. The formula simply extracts the index of the hyphen(-) first, and then returns the right substring, just before the hyphen(-) index. You’ll see the number is being separated from the percentage.

extracting right substring of number in case2

 

Step 2: In the column H and row 5 i.e cell H5 write the formula =MID(F5,FIND(“(“,F5)+1,FIND(“)”,F5)-FIND(“(“,F5)-1), as shown below, and press Enter. The formula simply extracts the next index to the “(“, and also the index of the “%”, then we use =MID() function, to provide the substring, between these indexes. 

extracting percentage in case2

 

Step 3: Similar, steps can be applied to Row 6. The final output looks as shown below:

output after separating, the number and percentage in case 2

 

CASE 2: Sepearting Row 7 and 8, Number and Percentage 

The third case explains, separating number and percentage, with, the space between number and percentage. The number is written on the left and the percentage is written on the right.  The only change in the formula of case1 and case3 is to change the hyphen(-) to space(” “) for separating the number.

Step 1: In column G and row 7 i.e cell G7 write the formula =LEFT(F7, FIND(” “, F7)-1), as shown below, and press Enter. The formula simply extracts the index of the space(” “) first and then returns the left substring, just before the space(” “) index. You’ll see the number is being separated from the percentage. 

NOTE: If there is any other symbol present in place of hyphen(-), you can replace the hyphen with that symbol. The above formula will still work.

extracting number in case of salary and percentage

 

Step 2: In the column H and row 7 i.e cell H7 write the formula =MID(F7,FIND(“(“,F7)+1,FIND(“)”,F7)-FIND(“(“,F7)-1), as shown below, and press Enter. The formula simply extracts the next index to the “(“, and also the index of the “%”, then we use =MID() function, to provide the substring, between these indexes. 

extracting percentage using mid function in case 3

 

Step 3: Similar, steps can be applied to Row 8. The final output looks as shown below:

separated number and percentage in case 3

 

NOTE: Make sure to enter the cell reference properly in your formula.  



Last Updated : 16 Oct, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads