Excel: extract and sum numerals from mixed text/numeral cell range

I have a large (30x20) grid of cells with data, and I want to extract
and then sum up certain numerals from this entire range.  The catch is
that the data is mixed numerals and text, as you'll see below.

Here's an abbreviated 3x3 example, with a value in each of the nine
cells:

V7.1   T     H
P1      A     T
B        V3   P4.5

If I just wanted to sum up the instances of "T" appearing, I could use
COUNTIF() for the entire range to come up with answer ("T" appears 2
times).  Easy enough.

But, what I'm trying to accomplish is to sum up the numerals associated
with other letters. Specifically, I'd like to sum up the numbers
associated with the letter "V"; there are two entries above, "V7.1" and
"V3", so I'd like to chop off the letters and sum the numerals.  The
answer would be 10.1 (the sum of 7.1 and 3). 

But how would I do this programmatically?

-- B.


---
Message posted from http://www.ExcelForum.com/

0
1/8/2004 9:04:00 PM
excel 39879 articles. 2 followers. Follow

3 Replies
4521 Views

Similar Articles

[PageSpeed] 9

On Thu, 8 Jan 2004 15:04:00 -0600, bssmith
<<bssmith.zqe8m@excelforum-nospam.com>> wrote:

>I have a large (30x20) grid of cells with data, and I want to extract
>and then sum up certain numerals from this entire range.  The catch is
>that the data is mixed numerals and text, as you'll see below.
>
>Here's an abbreviated 3x3 example, with a value in each of the nine
>cells:
>
>V7.1   T     H
>P1      A     T
>B        V3   P4.5
>
>If I just wanted to sum up the instances of "T" appearing, I could use
>COUNTIF() for the entire range to come up with answer ("T" appears 2
>times).  Easy enough.
>
>But, what I'm trying to accomplish is to sum up the numerals associated
>with other letters. Specifically, I'd like to sum up the numbers
>associated with the letter "V"; there are two entries above, "V7.1" and
>"V3", so I'd like to chop off the letters and sum the numerals.  The
>answer would be 10.1 (the sum of 7.1 and 3). 
>
>But how would I do this programmatically?
>
>-- B.

Well, here's a simple UDF that will do it, provided your data is in the format
you show above.

To enter the UDF, <alt><F11> opens the Visual Basic Editor.  Ensure your
project is highlighted in the Project Explorer, then Insert/Module and paste
the code below into the window that opens.

To use this, enter the function =SumDigByLtr(rg,ltr) where rg is the range you
wish to check, and ltr is the letter you want to get the sum for.  The letter
is case sensitive and must be enclosed in quotes.

e.g :   	=sumdigbyltr(E4:G6,"P")

===================
Option Explicit
Function SumDigByLtr(rg As Range, ltr As String) As Double
Dim c As Range

For Each c In rg
    If InStr(1, c.Text, ltr) > 0 Then
        SumDigByLtr = SumDigByLtr + Replace(c.Text, ltr, "")
    End If
Next c
End Function
=====================

--ron
0
1/8/2004 9:30:26 PM
B

Something like the following will do it. (Copy it to a standard module in
the workbook and then use it like a normal function), e.g if the data is in
A1:C3 and you are looking for cells beginning "P"

=sumvalues(A1:C3,"P")

In your example gives an answer of 5.5


Function SumValues(rArea As Range, sLetter As String) As Double
Dim myCell As Range
Dim dTempVal As Double
Application.Volatile True
For Each myCell In rArea
If Left(myCell.Value, 1) = sLetter Then
dTempVal = dTempVal + Mid(myCell.Value, 2, Len(myCell.Value) - 1)
End If
Next myCell
SumValues = dTempVal
End Function


-- 
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS


"bssmith >" <<bssmith.zqe8m@excelforum-nospam.com> wrote in message
news:bssmith.zqe8m@excelforum-nospam.com...
> I have a large (30x20) grid of cells with data, and I want to extract
> and then sum up certain numerals from this entire range.  The catch is
> that the data is mixed numerals and text, as you'll see below.
>
> Here's an abbreviated 3x3 example, with a value in each of the nine
> cells:
>
> V7.1   T     H
> P1      A     T
> B        V3   P4.5
>
> If I just wanted to sum up the instances of "T" appearing, I could use
> COUNTIF() for the entire range to come up with answer ("T" appears 2
> times).  Easy enough.
>
> But, what I'm trying to accomplish is to sum up the numerals associated
> with other letters. Specifically, I'd like to sum up the numbers
> associated with the letter "V"; there are two entries above, "V7.1" and
> "V3", so I'd like to chop off the letters and sum the numerals.  The
> answer would be 10.1 (the sum of 7.1 and 3).
>
> But how would I do this programmatically?
>
> -- B.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
1/8/2004 9:35:49 PM
You could do it with worksheet functions, too:

=SUM(IF(ISNUMBER(--SUBSTITUTE(UPPER(A1:C1925),"V","")),
                       --SUBSTITUTE(UPPER(A1:C1925),"V","")))

(all one cell, adjust the range accordingly)

This is an array formula.  Hit ctrl-shift-enter instead of enter.  If you do it
correctly, excel will wrap curly brackets {} around your formula.  (don't type
them yourself.)

"bssmith <" wrote:
> 
> I have a large (30x20) grid of cells with data, and I want to extract
> and then sum up certain numerals from this entire range.  The catch is
> that the data is mixed numerals and text, as you'll see below.
> 
> Here's an abbreviated 3x3 example, with a value in each of the nine
> cells:
> 
> V7.1   T     H
> P1      A     T
> B        V3   P4.5
> 
> If I just wanted to sum up the instances of "T" appearing, I could use
> COUNTIF() for the entire range to come up with answer ("T" appears 2
> times).  Easy enough.
> 
> But, what I'm trying to accomplish is to sum up the numerals associated
> with other letters. Specifically, I'd like to sum up the numbers
> associated with the letter "V"; there are two entries above, "V7.1" and
> "V3", so I'd like to chop off the letters and sum the numerals.  The
> answer would be 10.1 (the sum of 7.1 and 3).
> 
> But how would I do this programmatically?
> 
> -- B.
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
1/8/2004 11:54:27 PM
Reply:

Similar Artilces:

How do I toggle between worksheets in the same Excel file ?
Please tell me the keyboard shortcut to toggle between worksheets in the same Excel file. My OS is XP and am using Excel 2000. Ctrl+PageUp and Ctrl+PageDown Regards Rowan Sameer wrote: > Please tell me the keyboard shortcut to toggle between worksheets in the same > Excel file. My OS is XP and am using Excel 2000. ...

Opening Excel #5
When opening excel 10 different audio files have attached themselves to the start of each file. It's like the New Cd with Rod Stewart have transferred to excel. When launched, XL opens (or attempts to open) all files in your XLStart directory and, if an additional folder is designated, the files in Tools>Options>General Tab>At startup, open all file in___. Most users leave this option blank and remove all non-essential files from XLStart. -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays "GOLFBALL49" <anonymous@discussions.microsoft.com> wrot...

linking cells
I am trying to link cells from a column in workbook 1 to a column in workbook 2. (office 2003, winxp) I choose my destination row, put the '=' in the formula bar, copy the source row, and I see the formula in the formula bar but when I hit enter I get a 0 in cell one of the destination row and nothing in any of the other cells in the row. I also tried linking a three line address from one workbook to another. If I do it one line at a time, I have success. If I try to take the 3 line address in one block and link it all at once, I get the 'Value" error. Could someone please...

My sent mail appears in the destination without text. How can i c.
My sent mail appears in the destination without text. How can i correct this situation? It happens with all my sent mails. I have secure digital signature associated to all my e-mails but I dont know if it has something to do with it. Every person i send an e-mail receives it without text but when i see in my sent mails folder the text is there. Can the problem be from my ISP provider (vodafone?)? Has anyone had this problem? How can i solve it? Can anybody helpme? Do you have a virus scanner installed which integrates itself with Outlook? Uninstall this integration part o...

Text Clarity...
As I haven't seen this problem addressed previously I'm thinking there might be something wrong on my end. When either e-mailing a Publisher document to myself, in the body of an email, or checking the E-Mail preview option, the text in the document is not clear and concise. Am I missing something here? Thanks in advance. You have converted the text to an image. You can confirm that by trying to select some the text in your message. If you can't it is an image. Make sure that you have gone to Tools > Options > Web tab and clear the check box "Send entire publ...

Excel 97 / Excel 2000 compatibility problem?
Hello, I=B4m developing an Excel application with Excel 97. I have added a control toolbox of type textbox to one of=20 its worksheets. Until now it was no problem to run it with Excel 2000. But in the latest version the textbox disappears in Excel=20 2000. It is still visible on the sheet, but its name is=20 not more known in the Excel-VBA-Code nor is it visible in=20 the editor windows class list. Has anybody an explanation for this behaviour? Regards Michael ...

growing excel database
Hello everyone! It is possible to import data into excel from the web. It is also possible update this data IN THE SAME CELLS. In other words, the values that once occupied the cells are replaced with new values. That is a problem for me. I would like updated information to be pasted in rows above the old data. Is this possible? If so, how? Piotr, Yes, it is possible to import data into Excel from the web. Web queries are designed to do this and if a web query can't do what you need, then there are ways to automate Internet Explorer (i.e. IE Automation). You can record a m...

cell wrapping
I have used page setup to fit an large table onto an A3 sheet. The cells are the right size but I cannot get the font to fill the cells. The font is tiny and difficult to read. How can I increase the size of the font on a page? --- Message posted from http://www.ExcelForum.com/ Click on the top left corner of the page (between the A and the one) i should highlight the whole page. right click in the highlighted area, Format cells - Alignment - uncheck "Shrink to fit" - OK That should work Jennifer baldrik123 wrote: > *I have used page setup to fit an large table onto ...

Sorting issue
Hello, I am having a problem sorting two columns (one is the street name and the other is the street number eg. 321 Smith St) in Excel 2007 by using a custom sort. Let's say that the data in column A is the number and B is the street name. I have the sort setup to sort first by B then by A. The problem is with the numbers. Some numbers are out of order in the spreadsheet although the majority are correct. I have formatted the number column (A) as a number as well. I have also tried creating a new spreadsheet and pasting (paste special) the data into it with the same result. Can anyo...

Excel sheet
I want to increase value by 1 of every cell which I entered by using formula. How can do this? I want automatic result in excel I am getting problem by this You are in a Microsoft Publisher ng. But if you want to number a column, i.e., line numbers, this may be what you want In A1 put 1 In A2 put =1+a1 Now copy A2 down to whatever you want. I think this is how it goes in Excel; I'm a Lotus 1-2-3 user and the formuli are a bit different. Have a Merry! -- Don Vancouver, USA "vasanth" <u40119@uwe> wrote in message news:7d26017dc9997@uwe... >I want to incr...

How to make a cell show totals
What type of fomual can I use to have a cell show a toal dollar amount plus additional increase? (i.e. if I have a total of $950 in one cell, I have for additional cells and want to show 10%, 15%, 20%, 25% growth, is there a fomula that can show this patten?) It's best if you put your percentages in separate cells; that way they are easier to change if so desired As an example, put the $950 in cell B1, then the 10%-25% in cells A2 through A5 In cells B2 use the formula =$B$1*(1+$A2) Then copy it down to cells B3:B5 "Don" wrote: > What type of fomual can I use to ha...

Strange Excel behaviour
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hi there, <br> I'm writing to you because I'm driving crazy with excel. <br> Everything works quite fine, but, when entering numbers in a cell, Excell automaticly divides it per 100!! so I enter for example 4, and it writes 0,04!! <br> any idea about the reasons why?? <br> thanks a lot <br> Carlo Well, sure... And if you'd queried the group before posting you'd know why as well :-) It's been asked & answered numerous times: Go to Excel> Preferences&...

complicated excel template
I am on the beginning phases of a manufacturing template. Hopefully som of you can help me out. I need to determine how many parts of a certai item I can obtain from a determined square foot of material. Fo example: how many parts of 1" x 1.5" widgets can I get from a 48" x 96 sheet -- Message posted from http://www.ExcelForum.com You could use something simple like: =INT(C1/A1)*INT(D1/B1) where A1 = part width, B1 = part length, C1 = sheet width, D1 = sheet length. Of course this does not take into account any unique cutting patterns that minimize scrap. It's assume...

Planning training in Excel
I have been tasked to organise training for my company and they have asked me to use MS Project, which has totally blown my mind. Having done some investigating Project will not give me the answers that I need surprisingly, but I am hoping Excel will I am really trying hard to figure a way of schedulign training for 200 individuals, with 20 different modules (fixed dates) and take into account holidays. So for instance Joe Bloggs needs to attend Module A, B & D over the next 3 months, but he also has 2 weeks holiday booked. There are four other people in his department but I can only all...

Auto-changing of Excel Files' name
Ever since we changed our server's O/S from Windows Server NT 4.0 to Windows Server 2003, all excel files' name will get changed by itself when they are accessed from any terminal within the LAN, which is running Windows 98 or Windows XP. What could be the cause(s) of this strange problem? Any solution? ...

How do I change the width of an individual cell?
I am trying to adjust the width of an individual cell and all I am able to do is adjust the width of the entire column. Any suggestions? Thank you That is all you can do. Ola "Tabitha" <Tabitha@discussions.microsoft.com> skrev i meddelandet news:E138E608-96BB-4888-B886-36598D36E3CF@microsoft.com... >I am trying to adjust the width of an individual cell and all I am able to >do > is adjust the width of the entire column. Any suggestions? > > Thank you You can select two or more cells and merge them (Format>Cells, Alignment tab) However, this can cau...

Create drop down list in Excel 2007
Hi Could you please share with me how to create drop down list in Excel 2007? Thanks in advanced What version of Excel are you using? "lee-sc" wrote: > Hi > > Could you please share with me how to create drop down list in Excel 2007? > > Thanks in advanced > Sorry Lee-sc, hit the send button to quick in my previous post. In the Data menu, choose "Data Valiation", then "Data Validation". Under Settings, select "List" in he "Allow" drop down box. In the "Source" field, type in or se...

how can i set up an automatic "date update" in excel?
In other words, i would like excel to update this cell with the current date every time when i open the document. Can i do that? one way: =TODAY() In article <D8818AFF-8489-4FF1-B94D-A08FB0B45F01@microsoft.com>, "Litzyam" <Litzyam@discussions.microsoft.com> wrote: > In other words, i would like excel to update this cell with the current date > every time when i open the document. Can i do that? One way =today() HTH "Litzyam" wrote: > In other words, i would like excel to update this cell with the current date > every time when i open t...

Outlook 2000 mailbox cannot view embedded text in message
I'm working with Exchange 2003 clients that use Outlook 2000 to receive SMTP mail notifications from a SQL Server. The SMTP messages have text attachments with the output of queries put into the messages. However, on one user's mailbox, I can't see the text in the message body or as an attachment, only the subject line and sender come through. When the use the Outlook Web client, the same result happens. Is their some specific security setting to look for? Please help. Thanks. -- ************************************************* Andy S. MCSE NT/2000, MCDBA SQL 7/200...

Excel 95 to Excel 2003
Anyone know if it's possible to convert Excel 95 files to Excel 2003? If so, how. Thanks in advance Open the file in 2003 and SAVE as you would a file created in 2003. If not absolutely positive, save as a different name. -- Don Guillett SalesAid Software donaldb@281.com "Cooper" <kcooper2@austin.rr.com> wrote in message news:uqLpEuG4EHA.1292@TK2MSFTNGP10.phx.gbl... > Anyone know if it's possible to convert Excel 95 files to Excel 2003? If > so, how. > > Thanks in advance > > Open in XL 2003, when you close it, it should, ask you if you w...

Exporting the subreports to an excel
Hi , once i am able to create the sub reports when i export this to an excel i am not able to see the data.. can you please help me out with this.. Thanks in Advance Kasaiah ...

Excel appearance
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel when I open up a worksheet the view is too small so I zoom to 150% which is perfect. I cannot find a way to keep this preference as my default when opening other worksheets. i am a new user for Mac. thanks You can't do this in Excel 2008 because it has no VBA. Each workbook will open at the zoom ratio it was last saved at. In the next version of Excel, you will be able to make a simple macro to set the zoom on opening. Cheers On 17/03/10 5:20 AM, in article 59bb557f.-1@webcrossing.JaKIaxP2ac0, ...

Generated Excel 97 documents limited to one worksheet
Hi everyone, I read in several articles that if I want to generate an Excel 97 document dynamically, using ASP for example, by setting the content type to application/vnd.ms-excel, I read that in this case, the document is limited to one worksheet and the user will only be able to view one document max. at a time. Actually, I tried it myself, and it's true. The moment I open a second browser window and try to access the same page that generates the Excel document, the first page goes blank. My quetion is, whether there's an official Microsoft document of MSDN KB article that states t...

Excel formula bar
I want to insert in my formula bar the = sign as an function with my mouse - as I was able to do with my older excell. The 2003 excel shows the fx sign, which I do not want Thanks. Hi Jacko! Try: Tools > Customize > Commands Tab Under Insert you'll find the = sign that you can drag to a toolbar. It doesn't work the same as earlier versions of Excel but does what you ask. -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "Jacko Maree" &...

How to change font size on formula bar in Excell 2007
I don't find Tools>Options>General (as suggested in other post answers) in Excel 2007. On the Office button there is an Excel Options but it doesn't provide a method of changing font size on the office but. My font is so small I can barely see it. Office button>ExcelOptions>Popular tab>in the "When creating new workbooks" section, choose font and font size -- Kind regards, Niek Otten Microsoft MVP - Excel "jimwillie" <jimwillie@discussions.microsoft.com> wrote in message news:588AAC05-0F52-404E-AA01-128E70E02D0B@microso...