# Percentage from cell containing numbers and text

#### AWatson

##### New Member
Hi,

Is it possible to calculate a percentage from a cell that contains two numbers and text within the same cell? And is this possible without separating the numbers into separate cells?

E.g. If A1 contained the value "4 (19)", is it possible to create a formula to use the cell contents to calculate a percentage of 21% i.e. 4 of 19?

Similarly, if B1 contained the value "10 (20)", to calculate this as 50% (formula result in B2)?

The second number will always be in brackets.

Many thanks,

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Try this.

Excel Workbook
AB
14 (19)21.05%
210 (20)50.00%
3
Percentage

=LEFT(A1,FIND(" ",A1))/-MID(A1,FIND("(",A1),FIND(")",A1))

The first part takes the number left of the space and the next part looks at the number in the parenthesis. The second part required a minus sign because excel reads the number as a negative.

Try this.

Excel Workbook
AB
14 (19)21.05%
210 (20)50.00%
3**
Percentage

You answered before I could finish ! Thanks for the different perspective. I am constantly amazed at all the different ways Excel can handle things.

=LEFT(A1,FIND(" ",A1))/-MID(A1,FIND("(",A1),FIND(")",A1))

The first part takes the number left of the space and the next part looks at the number in the parenthesis. The second part required a minus sign because excel reads the number as a negative.
Chris

It may not be possible with the OP's data but the blue part would cause an error with your formula if there is a negative in the parentheses. For example try:
4 (-19)

The formula in the first reply works perfectly. Thanks very much for your help.

Is there a way to deal with errors e.g. if the both numeric values in A1 are zeros i.e. "0 (0)" and the result is #DIV/0! or if the cell is blank with data yet to be entered and the result is #VALUE!

Is there a way to deal with errors e.g. if the both numeric values in A1 are zeros i.e. "0 (0)" and the result is #DIV/0! or if the cell is blank with data yet to be entered and the result is #VALUE!
try this:

=IF(OR(A1="",COUNTIF(A1,"*(0)*")),"",LEFT(A1,FIND(" ",A1)-1)/SUBSTITUTE(REPLACE(A1,1,FIND("(",A1),""),")",""))

That's brilliant - thanks again for your help. I would never have managed that on my own!!

Chris

It may not be possible with the OP's data but the blue part would cause an error with your formula if there is a negative in the parentheses. For example try:
4 (-19)

Very good point...I did not consider the effects of a negative number. Thanks for pointing that out.

Replies
8
Views
203
Replies
7
Views
281
Replies
5
Views
155
Replies
6
Views
414
Replies
6
Views
771

1,218,560
Messages
6,143,204
Members
450,469
Latest member
brent3162

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back