Percents and Text in one cell - need to add percents only
 

Hello!

I've just received a spreadsheet that has in it cells containing both
percents and text (along with some other numbers) in the same cell. We need
to sum just the percentages.

Ex:
A1 contains 3.12% [Alt+Enter] at 122 days
B1 contains 4.56% [Alt+Enter] at 36 days
, etc.

I need a formula that will just add the percentages from those cells.

Any ideas?

Thanks,
Elf

Percents and Text in one cell - need to add percents only
 

Hi elfmajesty

Is there any structured format to the data? Are all of the percentages x.xx,
or are some xx.xx or x.xxx?
It may be worth trying Data/Text to Columns. If you set the first option to
delimited and have a look at the result in the second dialog box, it may do.
Make sure you backup your data before you start playing, though!

Andy.

"elfmajesty" wrote in message
...
Hello!

I've just received a spreadsheet that has in it cells containing both
percents and text (along with some other numbers) in the same cell. We
need
to sum just the percentages.

Ex:
A1 contains 3.12% [Alt+Enter] at 122 days
B1 contains 4.56% [Alt+Enter] at 36 days
, etc.

I need a formula that will just add the percentages from those cells.

Any ideas?

Thanks,
Elf

Percents and Text in one cell - need to add percents only
 

Andy,

Thanks for the thought, but that won't work. We need to keep the data
exactly "As is" and cannot split it between cells, even on a hidden working
sheet. The percentages are all 2 decimal places i.e., 4.25%, 14.42%, etc.
but because they are within cells containing text they are not formatted as
percentage numbers.

My thoughts are somehow within the formula to extract the numbers before the
percentage sign or something. I just don't know.

If you come up with any other thoughts, please feel free to post.

Thanks.
Elf

"Andy" wrote:

Hi elfmajesty

Is there any structured format to the data? Are all of the percentages x.xx,
or are some xx.xx or x.xxx?
It may be worth trying Data/Text to Columns. If you set the first option to
delimited and have a look at the result in the second dialog box, it may do.
Make sure you backup your data before you start playing, though!

Andy.

"elfmajesty" wrote in message
...
Hello!

I've just received a spreadsheet that has in it cells containing both
percents and text (along with some other numbers) in the same cell. We
need
to sum just the percentages.

Ex:
A1 contains 3.12% [Alt+Enter] at 122 days
B1 contains 4.56% [Alt+Enter] at 36 days
, etc.

I need a formula that will just add the percentages from those cells.

Any ideas?

Thanks,
Elf

Percents and Text in one cell - need to add percents only
 

Hi

One way would be to use a formula like this:
=LEFT(D10,FIND("%",D10))

Hope this helps.
Andy.

"elfmajesty" wrote in message
...
Andy,

Thanks for the thought, but that won't work. We need to keep the data
exactly "As is" and cannot split it between cells, even on a hidden
working
sheet. The percentages are all 2 decimal places i.e., 4.25%, 14.42%,
etc.
but because they are within cells containing text they are not formatted
as
percentage numbers.

My thoughts are somehow within the formula to extract the numbers before
the
percentage sign or something. I just don't know.

If you come up with any other thoughts, please feel free to post.

Thanks.
Elf

"Andy" wrote:

Hi elfmajesty

Is there any structured format to the data? Are all of the percentages
x.xx,
or are some xx.xx or x.xxx?
It may be worth trying Data/Text to Columns. If you set the first option
to
delimited and have a look at the result in the second dialog box, it may
do.
Make sure you backup your data before you start playing, though!

Andy.

"elfmajesty" wrote in message
...
Hello!

I've just received a spreadsheet that has in it cells containing both
percents and text (along with some other numbers) in the same cell. We
need
to sum just the percentages.

Ex:
A1 contains 3.12% [Alt+Enter] at 122 days
B1 contains 4.56% [Alt+Enter] at 36 days
, etc.

I need a formula that will just add the percentages from those cells.

Any ideas?

Thanks,
Elf