Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: SUM Numbers & Text In Same Cell

1. Member
Join Date
18th February 2006
Posts
66

## SUM Numbers & Text In Same Cell

I have several cells I need to sum that are MB K size related. IE.....

Circuits per Router....
155 MEG
85 MEG
100 MEG
768 K
512 K
76 MEG

need to total these
add up the MEG as 416MEG
and the K 1.2MEG
Total 417.2MEG

does this make sense ???

Excel Video Tutorials / Excel Dashboards Reports

2. Member
Join Date
18th February 2006
Posts
66

## Re: Add Numbers With Text In Same Cell

Any Takers ?
I am trying to search from left to first space, this will be the number, second
search will be the text, either MB or KB how can I store the vars for multiple cells and two different items parsed ???

Excel Video Tutorials / Excel Dashboards Reports

3. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,478

## Re: Add Numbers With Text In Same Cell

Hi,

=SUM(IF(ISNUMBER(SEARCH("k",A1:A6)),SUBSTITUTE(A1:A6," K","")+0))/1000+SUM(IF(ISNUMBER(SEARCH("meg",A1:A6)),SUBSTITUTE(A1:A6," MEG","")+0))

To Enter the array formula hold down Ctrl and Shift while pushing Enter.

4. ## Re: Add Numbers With Text In Same Cell

Use Data>Text to Columns.. to split the numbers and text into 2 different columns then do a SUMIF like

=SUMIF(B1:B20,"MEG",A1:A20)

Or, give the 2 columns headings and use a simple Pivot Table

Or

=SUM(IF(MID(A1:A6,FIND(" ",A1:A6)+1,256)="MEG",VALUE(LEFT(A1:A6,FIND(" ",A1:A6)-1)),0))

Where there are no blanks in the range A1:A6

To Enter the array formula hold down Ctrl and Shift while pushing Enter.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno