Home 
Search 
Today's Posts 
#11




Need to add figures based on alpha characters in the same cell
I don't know what the  means that you have
just before the word LEFT LEFT is a *text* function so it returns a *text* value. We need to convert the result of LEFT into numeric numbers that SUM can add together. SUM ignores text. The double unary () coerces the text numbers returned by LEFT into numeric numbers which SUM can then add up.  Biff Microsoft Excel MVP "Frustrated in Iowa" wrote in message ... Do you know how I can use your formula below in Google Docs(spreadsheets). I keep getting an error. Google seems to have all the functions you used  however I don't know what the  means that you have just before the word LEFT (The formula works great in Excel) "T. Valko" wrote: 10cc 5ca 20cc 5cc 10ca 5ca In the above, the cc total should be $30 (i.e. 10cc + 20cc) I think it should be 35. 10+20+5 Try this array formula** : =SUM(IF(RIGHT(A1:F1,2)="cc",LEFT(A1:F1,LEN(A1:F1)2))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER)  Biff Microsoft Excel MVP "Frustrated in Iowa" <Frustrated in wrote in message news My data is in the format of 55.45cc 34.00ca 23.21 ch ...... where the number represents a dollar amount of a sale and the letters indicate cc=credit card, ca=cash etc. Each number/character pair is in a single cell. e.g. 55.45cc is in one cell. A row of these numbers/characters like above represent a day of sales. I want to have totals for all credit cards, all cash, and all check. Hope this makes sense. If not here is an example (I have left out any numbers after the decimal) 10cc 5ca 20cc 5cc 10ca 5ca In the above, the cc total should be $30 (i.e. 10cc + 20cc) and the ca total should be $20 (i.e. 5ca + 10 ca + 5ca). 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
to extract numeric figures in left side od the alpha charectors pl  Excel Discussion (Misc queries)  
Using a cell w/Alpha numeric characters in mulplication formula  Excel Worksheet Functions  
Can you ID a cell that has both Alpha AND Numeric characters?  Excel Worksheet Functions  
Check for Alpha characters  Excel Discussion (Misc queries)  
Count unique alpha numeric "characters" in a common cell  Excel Worksheet Functions 