 # Formula

I have two no in one cell and i want addition of that two no in another cell which formula I have to use please guide
8 Replies

# Betreff: Formula

You can find precise details on how to quickly and precisely come up with a proposed solution in the link below

Welcome to your Excel discussion space!

Thank you for your understanding and patience

Wish you a nice day.

Nikolino

I know I don't know anything (Socrates)

# Re: Formula

IF you are using Excel 365 and IF your cells/range is called 'input' and IF each cell comprises two numbers separated by a comma, then

``````= LET(sep, ",",
p,      FIND(sep, input),
first,  MID(input, 1, p-1),
second, MID(input, p+1, 15),
first+second )``````

otherwise you need something different!

# Re: Formula

Is that like this? Yes

# Re: Formula

@Shailesh1982 Inspired by the contributions of @Peter Bartholomew and @Sergei Baklan , try this in case Peter's LET formula doesn't work for you: # Re: Formula

``=SUM(FILTERXML("<y><z>"&SUBSTITUTE(A1,",","</z><z>")&"</z></y>","//z"))``

# Re: Formula

Here is the simplest formula for the checksu.

For multi-digit numbers you have to expand the formula accordingly.

Example 1: "=MID(A1,1,1)+MID(A1,2,1)+MID(A1,3,1)", use without goosefoot.

Example 2: "=SUMPRODUCT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1) ", use without goosefoot.

Example 3: "{=SUM(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))}" , use without goosefoot.

*3 Array formulas must always be confirmed with the key combination "CTRL + SHIFT + ENTER"

The file with the examples is included.

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

# Re: Formula

As a comment, formula suggested by @Detlef Lewin is more flexible and could count any reasonable number of numbers within cell. To expand @Peter Bartholomew formula as variant

``````=LET(input, A1, sep, ",",
seps,  LEN(input)-LEN(SUBSTITUTE(input,sep,"")),
k,     SEQUENCE(seps+1),
start, IFERROR( FIND("|",SUBSTITUTE(input, sep,"|",k-1))+1,1),
end,   IFERROR( FIND("|",SUBSTITUTE(input, sep,"|",k)),LEN(input)+1),
SUM(--TRIM(MID(input, start, end-start)))
)``````