Summing Numerical part of Alpha Numeric !

General discussion on OpenOffice Calc and its spreadsheets.

Summing Numerical part of Alpha Numeric !

Postby Bryan L. Warfield » Wed Dec 24, 2014 9:09 am

Hello Everyone,

I need your assistance on a formula in Open Office Spread sheet.

I want to use below Excel formula in Open office Spread sheet. How do I use the same.

SUMPRODUCT(0+LEFT(A1:A4,MMULT(0+ISNUMBER(-MID(A1:A4,{1,2,3,4,5},1)),{1;1;1;1;1})))

This formula I am using to summing of Numerical part of Alphanumeric column.

4B
3456 A
1 BFD
8 BBB

With this formula I am getting a correct answer (4+3456+1+8=3469) in excel
The same output I am looking in Open Office.

Can you please assist me doing the same.

Regards, Bryan
Bryan L. Warfield
 
Posts: 29
Joined: Mon Mar 24, 2014 6:11 am

Re: Summing Numerical part of Alpha Numeric !

Postby Champ_AN » Thu Jan 01, 2015 11:51 am

Hi Everyone,

To do this follow the steps mention below:
1) Tools
3) Options
4) Calc
5) Calculate "Enable regular expressions in formulas" = ON

Code: Select all
A1   123abc
B1   =SEARCH("[^[:digit:]]";A1)
C1   =VALUE(LEFT(A1;B1-1))
D1   =MID(A1;B1;LEN(A1))


B1 gets the position of the first non-digit in A1, C1 gets the value of the first part, D1 gets the remaining string.
This does not work with Excel because Excel does not know advanced pattern matching by means of regular expressions.
Champ_AN
 
Posts: 19
Joined: Tue Mar 25, 2014 6:34 am

Re: Summing Numerical part of Alpha Numeric !

Postby Keya_LOT » Thu Jan 01, 2015 12:23 pm

Hallo


Use the given code. I hope it will help you.
Code: Select all
=SUM(VALUE(LEFT(A1:A4;SEARCH("[^0-9]";A1:A4)-1)))

as matrixformula
Keya_LOT
 
Posts: 24
Joined: Tue Mar 25, 2014 6:35 am


Return to OpenOffice.org Calc

Who is online

Users browsing this forum: Yahoo [Bot] and 1 guest

cron