Percentage from cell containing numbers and text

AWatson

New Member
Joined
Jun 29, 2008
Messages
12
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,
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,918
Office Version
365
Platform
Windows
Try this.

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

chrisdavis

New Member
Joined
Jun 25, 2010
Messages
20
=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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,918
Office Version
365
Platform
Windows

ADVERTISEMENT

=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)
 

AWatson

New Member
Joined
Jun 29, 2008
Messages
12
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!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,918
Office Version
365
Platform
Windows

ADVERTISEMENT

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),""),")",""))
 

AWatson

New Member
Joined
Jun 29, 2008
Messages
12
That's brilliant - thanks again for your help. I would never have managed that on my own!!
 

chrisdavis

New Member
Joined
Jun 25, 2010
Messages
20
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,503
Messages
5,511,696
Members
408,859
Latest member
willm57

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top