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

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" <elfmajesty@discussions.microsoft.com> wrote in message
news:B0E5E56E-A457-47ED-869D-DE6EF108D7BC@microsoft.com...
> 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

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" <elfmajesty@discussions.microsoft.com> wrote in message
> news:B0E5E56E-A457-47ED-869D-DE6EF108D7BC@microsoft.com...
> > 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
>
>
>

Hi

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

Hope this helps.
Andy.

"elfmajesty" <elfmajesty@discussions.microsoft.com> wrote in message
news:DBD5B4E0-C0AE-42C6-8D66-61DE01BF1B74@microsoft.com...
> 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" <elfmajesty@discussions.microsoft.com> wrote in message
>> news:B0E5E56E-A457-47ED-869D-DE6EF108D7BC@microsoft.com...
>> > 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
>>
>>
>>