Help to add only numeric value in a cell

[Sakthikumar Chinnasamy]

This is the value is present in A1 cell "E: 800 GBK: 400 GBG: 250 GBD: 89 GBC: 48 GBQ: 2 GB". I want result of either separate only numeric value or add the numeric value.

 

[r2c2]

@Sakthikumar Chinnasamy

The data person in me screams, "split the data using text to columns or PQ" and then add them up. Saves a ton of time and effort.

But the formula person in me wants to take up the challenge. So here you go. I am sure someone smarter will come along and post a shorter or simpler one.

Until then this array formula should work alright:

=SUM(IFERROR(TRIM(MID(A1, SMALL((MID(A1,ROW($H$1:$H$200),1)=":")*ROW($H$1:$H$200),ROW($H$1:$H$200))+1, SMALL((MID(A1,ROW($H$1:$H$200),3)=" GB")*ROW($H$1:$H$200),ROW($H$1:$H$200)) - SMALL((MID(A1,ROW($H$1:$H$200),1)=":")*ROW($H$1:$H$200),ROW($H$1:$H$200))-1)),0)+0)

You can set up a named formula for ROW(H1:H200) and shorten the formula as this.

=SUM(IFERROR(TRIM(MID(A1, SMALL((MID(A1,r.nums,1)=":")*r.nums,r.nums)+1, SMALL((MID(A1,r.nums,3)=" GB")*r.nums,r.nums) - SMALL((MID(A1,r.nums,1)=":")*r.nums,r.nums)-1)),0)+0)

 

[bosco_yip]

Try,

Sum the numeric values in a string.

A1 = E: 800 GBK: 400 GBG: 250 GBD: 89 GBC: 48 GBQ: 2 GB

B1, enter formula :

=SUMPRODUCT(IFERROR(0+TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}*99-98,99)),0))

Returned result is 1589

Regards
Bosco

 

[John Jairo V]

Hi to all!

Just another option using VBA:

Function SumOnlyNumbers&(R$, Optional Delim$ = " ")
    Dim a, i&, res&
   
    a = Split(R, Delim)
    For i = LBound(a) To UBound(a)
        If IsNumeric(a(i)) Then res = res + a(i)
    Next i
   
    Erase a: SumOnlyNumbers = res
End Function

Check File. Blessings to all!

 

[Sakthikumar Chinnasamy]

Thank you.. But it's not working for the below criteria:


B2= /usr/sap/ARP/ERS11: 358400 MB/usr/sap/ARP/ASCS00: 358400 MB/ASIA: 5120 MB/dump: 1024 MB/usr/sap/ARP/SCS20: 358400 MB/home: 2048 MB/var: 2560 MB/export: 358400 MB/var/adm/ras/livedump: 256 MB/staging/sapbackup: 358400 MB/mysap_AXP/autosys: 9216 MB/opt/bmc: 3072 MB/opt: 2304 MB/oracle: 102400 MB/mysap_ARP/utilities: 5120 MB/admin: 1024 MB/usr/sap/ARP/ERS21: 358400 MB/hpoms: 10240 MB/product/autotree: 5120 MB/usr: 9216 MB/corefiles: 1024 MB/: 1536 MB/staging: 6291456 MB/ZCA_Decom: 540672 MB/audit: 1024 MB/AZIA: 20480 MB/tmp: 5120 MB/saptech: 358400 MB/interfaces: 358400 MB/usr/sap: 50944 MB/var/nmon: 1024 MB/sapmnt/ARP: 358400 MB/depot: 281600 MB/usr/sap/transARP: 358400 MB

 

[r1c1]

You can use the formula in #2, just change " GB" to " MB".

 

[John Jairo V]

Hi to all!

Try this array formulae:

=SUM(--IFERROR(MID(A1,2+SEARCH("|",SUBSTITUTE(A1,":","|",ROW($1:$99))),MMULT(IFERROR(SEARCH("|",SUBSTITUTE(A1,{":"," MB"},"|",ROW($1:$99))),),{-1;1})-2),))

Blessings!

 

[bosco_yip]

For the data posted in Level # 5, this non-array formula worked in my Excel 2016.

=SUMPRODUCT(--TEXT(MID(TEXT(MID(SUBSTITUTE(A1," MB",REPT(" ",15)),ROW($1:$1200),15),),2,15),"0;;;\0"))

The returned result is 10,937,600

Edit :

Formula design concept between John and Bosco.

John's formula : Sum numbers in string between ":" and " MB" (post #7)

Bosco's formula : Sum numbers in string with " MB" in behind (post #8)

Bosco's formula : Sum numbers in string between " " (post #3)

Regards
Bosco

 

[John Jairo V]

Excellent formula @bosco_yip. Congrats!