# Formula for extracting a raw number

#### tmk0427

##### Board Regular
Cell A1 contains [28.93% - 08-Aug] Cell A2 contains [59.53% - 10-Jan] and cell A3 contains the formula

I'm trying to create a formula say in cell A3 that will just take the 28.93% without the percentage sign in say cell A1 and add it the cell A2 number of 59.53% without the percentage sign and average the two numbers to find the average of the two and be shown as a percentage.

I still want the dates and the percentage sign to show in the A1 and A2 cells. I hope that makes sense. 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.

#### Andrew Poulsom

##### MrExcel MVP
Welcome to the Board.

Try:

=AVERAGE(LOOKUP(9.99999999999999E+307,--(LEFT(A1,ROW(INDIRECT("1:"&LEN(A1)))))),LOOKUP(9.99999999999999E+307,--(LEFT(A2,ROW(INDIRECT("1:"&LEN(A2)))))))

#### Richard Schollar

##### MrExcel MVP
Hi

Try:

=(VALUE(LEFT(A1,FIND("%",A1)-1))+VALUE(LEFT(A2,FIND("%",A2)-1)))/(2*100)

and for mat A3 as %number format.

Regards

Richard

#### NBVC

##### Well-known Member
TRY:

=AVERAGE(VALUE(LEFT(A1,FIND("%",A1)-1)),VALUE(LEFT(A2,FIND("%",A2)-1)))&"%"

EDIT:

Actually, that will make the result a text string.

To make it numberical use:

=AVERAGE(VALUE(LEFT(A25,FIND("%",A25)-1)),VALUE(LEFT(B25,FIND("%",B25)-1)))/100

And format to Percentage and 2 decimals.

#### barry houdini

##### MrExcel MVP
Another alternative ...

=AVERAGE(LEFT(A1:A2,FIND("%",A1:A2)-1)/100)

confirmed with CTRL+SHIFT+ENTER

format as percentage

particularly useful if you have a larger range of cells, rather than just 2

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,168,091
Messages
5,857,303
Members
431,870
Latest member
Muratculous

### 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