| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Summing first 2 characters of a cell (some non-numeric)
Hi there,
I am trying to sum the first 2 characters of a cell which works fine when they are all numeric but when one isn't it falls over The data: 12 hours 0 mins Pending 4 hours 0 mins 8 hours 0 mins Formula used: =SUMPRODUCT(--LEFT(AA19:AA22,2)) Which as I said works fine if there's a value instead of pending. I don't really want to use a holding cell for the values as there is a chance it could get overwritten by the user (they cut and paste into the spread sheet) Is there a way to filter the result of the left to ignore "Pe" (this is the only possible non numeric value) Thank you! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Summing first 2 characters of a cell (some non-numeric)
Try this array formula
=SUM(IF(ISNUMBER(--LEFT(AA19:AA22,FIND(" ",AA19:AA22)-1)),--(LEFT(AA19:AA22,FIND(" ",AA19:AA22)-1)))) as an array formula commit with Ctrl-Shift-Enter -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "KidMalicious" wrote in message ... Hi there, I am trying to sum the first 2 characters of a cell which works fine when they are all numeric but when one isn't it falls over The data: 12 hours 0 mins Pending 4 hours 0 mins 8 hours 0 mins Formula used: =SUMPRODUCT(--LEFT(AA19:AA22,2)) Which as I said works fine if there's a value instead of pending. I don't really want to use a holding cell for the values as there is a chance it could get overwritten by the user (they cut and paste into the spread sheet) Is there a way to filter the result of the left to ignore "Pe" (this is the only possible non numeric value) Thank you! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Summing first 2 characters of a cell (some non-numeric)
That didn't work bob it just evaluates to zero.
but I followed your thinking and just replaced you Find with the number 2 like this =SUM(IF(ISNUMBER(--LEFT(AA19:AA22,2)),--(LEFT(AA19:AA22,2)))) and it worked perfectly! thanks a million for all the help "Bob Phillips" wrote: Try this array formula =SUM(IF(ISNUMBER(--LEFT(AA19:AA22,FIND(" ",AA19:AA22)-1)),--(LEFT(AA19:AA22,FIND(" ",AA19:AA22)-1)))) as an array formula commit with Ctrl-Shift-Enter -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "KidMalicious" wrote in message ... Hi there, I am trying to sum the first 2 characters of a cell which works fine when they are all numeric but when one isn't it falls over The data: 12 hours 0 mins Pending 4 hours 0 mins 8 hours 0 mins Formula used: =SUMPRODUCT(--LEFT(AA19:AA22,2)) Which as I said works fine if there's a value instead of pending. I don't really want to use a holding cell for the values as there is a chance it could get overwritten by the user (they cut and paste into the spread sheet) Is there a way to filter the result of the left to ignore "Pe" (this is the only possible non numeric value) Thank you! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Summing first 2 characters of a cell (some non-numeric)
Mine was more generic, it was picking up any number before the first space.
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "KidMalicious" wrote in message ... That didn't work bob it just evaluates to zero. but I followed your thinking and just replaced you Find with the number 2 like this =SUM(IF(ISNUMBER(--LEFT(AA19:AA22,2)),--(LEFT(AA19:AA22,2)))) and it worked perfectly! thanks a million for all the help "Bob Phillips" wrote: Try this array formula =SUM(IF(ISNUMBER(--LEFT(AA19:AA22,FIND(" ",AA19:AA22)-1)),--(LEFT(AA19:AA22,FIND(" ",AA19:AA22)-1)))) as an array formula commit with Ctrl-Shift-Enter -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "KidMalicious" wrote in message ... Hi there, I am trying to sum the first 2 characters of a cell which works fine when they are all numeric but when one isn't it falls over The data: 12 hours 0 mins Pending 4 hours 0 mins 8 hours 0 mins Formula used: =SUMPRODUCT(--LEFT(AA19:AA22,2)) Which as I said works fine if there's a value instead of pending. I don't really want to use a holding cell for the values as there is a chance it could get overwritten by the user (they cut and paste into the spread sheet) Is there a way to filter the result of the left to ignore "Pe" (this is the only possible non numeric value) Thank you! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Summing first 2 characters of a cell (some non-numeric)
I know, but it didn't work when I put it in, so I changed it
anyway thanks for your help I appreciated it! "Bob Phillips" wrote: Mine was more generic, it was picking up any number before the first space. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "KidMalicious" wrote in message ... That didn't work bob it just evaluates to zero. but I followed your thinking and just replaced you Find with the number 2 like this =SUM(IF(ISNUMBER(--LEFT(AA19:AA22,2)),--(LEFT(AA19:AA22,2)))) and it worked perfectly! thanks a million for all the help "Bob Phillips" wrote: Try this array formula =SUM(IF(ISNUMBER(--LEFT(AA19:AA22,FIND(" ",AA19:AA22)-1)),--(LEFT(AA19:AA22,FIND(" ",AA19:AA22)-1)))) as an array formula commit with Ctrl-Shift-Enter -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "KidMalicious" wrote in message ... Hi there, I am trying to sum the first 2 characters of a cell which works fine when they are all numeric but when one isn't it falls over The data: 12 hours 0 mins Pending 4 hours 0 mins 8 hours 0 mins Formula used: =SUMPRODUCT(--LEFT(AA19:AA22,2)) Which as I said works fine if there's a value instead of pending. I don't really want to use a holding cell for the values as there is a chance it could get overwritten by the user (they cut and paste into the spread sheet) Is there a way to filter the result of the left to ignore "Pe" (this is the only possible non numeric value) Thank you! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Summing first 2 characters of a cell (some non-numeric)
I'll bet that is because the newsreader wrapped it. This one should work,
even for you =SUM(IF(ISNUMBER(--LEFT(AA19:AA22, FIND(" ",AA19:AA22)-1)),--(LEFT(AA19:AA22, FIND(" ",AA19:AA22)-1)))) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "KidMalicious" wrote in message ... I know, but it didn't work when I put it in, so I changed it anyway thanks for your help I appreciated it! "Bob Phillips" wrote: Mine was more generic, it was picking up any number before the first space. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "KidMalicious" wrote in message ... That didn't work bob it just evaluates to zero. but I followed your thinking and just replaced you Find with the number 2 like this =SUM(IF(ISNUMBER(--LEFT(AA19:AA22,2)),--(LEFT(AA19:AA22,2)))) and it worked perfectly! thanks a million for all the help "Bob Phillips" wrote: Try this array formula =SUM(IF(ISNUMBER(--LEFT(AA19:AA22,FIND(" ",AA19:AA22)-1)),--(LEFT(AA19:AA22,FIND(" ",AA19:AA22)-1)))) as an array formula commit with Ctrl-Shift-Enter -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "KidMalicious" wrote in message ... Hi there, I am trying to sum the first 2 characters of a cell which works fine when they are all numeric but when one isn't it falls over The data: 12 hours 0 mins Pending 4 hours 0 mins 8 hours 0 mins Formula used: =SUMPRODUCT(--LEFT(AA19:AA22,2)) Which as I said works fine if there's a value instead of pending. I don't really want to use a holding cell for the values as there is a chance it could get overwritten by the user (they cut and paste into the spread sheet) Is there a way to filter the result of the left to ignore "Pe" (this is the only possible non numeric value) Thank you! |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Summing first 2 characters of a cell (some non-numeric)
lol @ even for me :-)
Yep that worked 100% again thanks for all the help! "Bob Phillips" wrote: I'll bet that is because the newsreader wrapped it. This one should work, even for you =SUM(IF(ISNUMBER(--LEFT(AA19:AA22, FIND(" ",AA19:AA22)-1)),--(LEFT(AA19:AA22, FIND(" ",AA19:AA22)-1)))) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "KidMalicious" wrote in message ... I know, but it didn't work when I put it in, so I changed it anyway thanks for your help I appreciated it! "Bob Phillips" wrote: Mine was more generic, it was picking up any number before the first space. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "KidMalicious" wrote in message ... That didn't work bob it just evaluates to zero. but I followed your thinking and just replaced you Find with the number 2 like this =SUM(IF(ISNUMBER(--LEFT(AA19:AA22,2)),--(LEFT(AA19:AA22,2)))) and it worked perfectly! thanks a million for all the help "Bob Phillips" wrote: Try this array formula =SUM(IF(ISNUMBER(--LEFT(AA19:AA22,FIND(" ",AA19:AA22)-1)),--(LEFT(AA19:AA22,FIND(" ",AA19:AA22)-1)))) as an array formula commit with Ctrl-Shift-Enter -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "KidMalicious" wrote in message ... Hi there, I am trying to sum the first 2 characters of a cell which works fine when they are all numeric but when one isn't it falls over The data: 12 hours 0 mins Pending 4 hours 0 mins 8 hours 0 mins Formula used: =SUMPRODUCT(--LEFT(AA19:AA22,2)) Which as I said works fine if there's a value instead of pending. I don't really want to use a holding cell for the values as there is a chance it could get overwritten by the user (they cut and paste into the spread sheet) Is there a way to filter the result of the left to ignore "Pe" (this is the only possible non numeric value) Thank you! |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Summing first 2 characters of a cell (some non-numeric)
Glad we resolved that <go
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "KidMalicious" wrote in message ... lol @ even for me :-) Yep that worked 100% again thanks for all the help! "Bob Phillips" wrote: I'll bet that is because the newsreader wrapped it. This one should work, even for you =SUM(IF(ISNUMBER(--LEFT(AA19:AA22, FIND(" ",AA19:AA22)-1)),--(LEFT(AA19:AA22, FIND(" ",AA19:AA22)-1)))) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "KidMalicious" wrote in message ... I know, but it didn't work when I put it in, so I changed it anyway thanks for your help I appreciated it! "Bob Phillips" wrote: Mine was more generic, it was picking up any number before the first space. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "KidMalicious" wrote in message ... That didn't work bob it just evaluates to zero. but I followed your thinking and just replaced you Find with the number 2 like this =SUM(IF(ISNUMBER(--LEFT(AA19:AA22,2)),--(LEFT(AA19:AA22,2)))) and it worked perfectly! thanks a million for all the help "Bob Phillips" wrote: Try this array formula =SUM(IF(ISNUMBER(--LEFT(AA19:AA22,FIND(" ",AA19:AA22)-1)),--(LEFT(AA19:AA22,FIND(" ",AA19:AA22)-1)))) as an array formula commit with Ctrl-Shift-Enter -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "KidMalicious" wrote in message ... Hi there, I am trying to sum the first 2 characters of a cell which works fine when they are all numeric but when one isn't it falls over The data: 12 hours 0 mins Pending 4 hours 0 mins 8 hours 0 mins Formula used: =SUMPRODUCT(--LEFT(AA19:AA22,2)) Which as I said works fine if there's a value instead of pending. I don't really want to use a holding cell for the values as there is a chance it could get overwritten by the user (they cut and paste into the spread sheet) Is there a way to filter the result of the left to ignore "Pe" (this is the only possible non numeric value) Thank you! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I pull just the numeric characters in a cell? | Excel Discussion (Misc queries) | |||
| Replacing characters in numeric text strings and SUMming | Excel Discussion (Misc queries) | |||
| Can you ID a cell that has both Alpha AND Numeric characters? | Excel Worksheet Functions | |||
| remove non-numeric characters from a cell | Excel Discussion (Misc queries) | |||
| Allow Numeric Characters Only In A Cell | Excel Discussion (Misc queries) | |||
Linear Mode
