Adding multiple values in a single cell to create a sum in an empty cell

[Deleted]

Hello! I am trying to create a formula in which an empty cell returns the sum of values entered in the previous cell. For example, Cell S2 has "1.00 (alt+enter) 1.00 (alt+enter)" and I would like Cell T2 to return "2.00" after adding the figures in Cell S2. It is important to keep the format of S2 as close as possible, as they line up with another field's data.

We are using Microsoft Office Standard 2016, with Windows 10 Pro, Version 22H2.

Thank you very much!

[#2]

@Deleted 

Try this one:

=SUM(1*TEXTSPLIT(S2,CHAR(10)))

[#3]

Hi @Patrick2788. Thank you! I'm getting the #NAME? error.

[#4]

Which version of Excel are you using? It seems to be something other than 365.

[#5]

We are using Microsoft Office Standard 2016.

[#6]

@Deleted 

This formula will pull  up to 4 numbers and sum them. Another option might be FILTERXML but with no spilling there's going to be some messy extraction either way.

=IFERROR(LEFT(S2,FIND(CHAR(10),S2)-1)*1,0)+IFERROR(MID(S2,FIND(CHAR(10),S2)+1,FIND(CHAR(10),S2,FIND(CHAR(10),S2)+1)-(FIND(CHAR(10),S2)+1))*1,0)+IFERROR(MID(S2,FIND(CHAR(10),S2,FIND(CHAR(10),S2)+1)+1,FIND(CHAR(10),S2,1+FIND(CHAR(10),S2,FIND(CHAR(10),S2)+1))-(FIND(CHAR(10),S2,FIND(CHAR(10),S2)+1)+1)),0)+IFERROR(MID(S2,FIND(CHAR(10),S2,1+FIND(CHAR(10),S2,FIND(CHAR(10),S2)+1))+1,10)*1,0)

[#7]

It's doing something, but only returning 1.00 instead of 2.00. I really appreciate your help!

[#8]

You might have more non-number characters in that cell besides the carriage return. You could use:
=LEN(S2)

This will determine the length of how many characters are in the cell.

[#9 · Accepted solution]

@Deleted 

Try this:

Select T2.

On the Formulas tab of the ribbon, click Define Name.

Enter Total in the Name box, and in the Refers to box, enter the formula

=EVALUATE(SUBSTITUTE(LEFT(!S2,LEN(!S2)-(RIGHT(!S2)=CHAR(10))),CHAR(10),"+"))

Click OK.

Enter the formula =Total in T2.

This can be filled down if required.

[#10]

@Patrick2788 Thank you for your help! I appreciate you!

[#11]

@Hans Vogelaar Thank you so much! That works perfectly. I really appreciate the help! -Shannon