New Contributor

# 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!

Please include the following info to help others answer your question

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)))
)``````