Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #11   Report Post  
Old November 13th 08, 09:16 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
to extract numeric figures in left side od the alpha charectors pl pol Excel Discussion (Misc queries) 1 August 20th 08 08:29 AM
Using a cell w/Alpha numeric characters in mulplication formula MAJ0116 Excel Worksheet Functions 4 February 10th 07 01:07 AM
Can you ID a cell that has both Alpha AND Numeric characters? Phil Excel Worksheet Functions 5 April 18th 06 09:32 PM
Check for Alpha characters TimE Excel Discussion (Misc queries) 4 November 10th 05 12:31 AM
Count unique alpha numeric "characters" in a common cell WIM4246 Excel Worksheet Functions 4 December 11th 04 02:27 AM


All times are GMT +1. The time now is 02:07 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017