Thread: Extract add numbers from text w/blank cells

Hi all,

I hope someone can help on this one. I am sooo stumped.

I have one column with numbers as text on the left and right separated by "..", i.e.,

4.56..12.01
9.12..0.34
11.67..5.46

This is data that was stored in this fashion and I don't know why. I have about 1500 of these in one column.

I need to add the left-most numbers and the right-most numbers and then a total. Attached please find a sample workbook.

If possible I would really appreciate a formula that can add the numbers on the left and another formula to add the numbers on the right.

Thank you in advance for helping with or just reading this post.

I have made left side (You can figure out the right side in similar way)

ok. with right side was worst but it works

=1*LEFT(B4,FIND("..",B4)-1)      left side    no problem
=1*RIGHT(B1,FIND("..",B1)-1)     some errors

have to change -1 in some numbers

Use two helper columns say D and E

In D enter

=LEFT(B1,FIND("..",B1)-1)+0

and pull down as needed
In E enter

=RIGHT(B1,LEN(B1)-FIND("..",B1)-1)+0

and pull down

You can combine in one column if you like

=RIGHT(B1,LEN(B1)-FIND("..",B1)-1)+LEFT(B1,FIND("..",B1)-1)

Thank you all... but alas not solved yet since I was aiming at not having to use helper columns -see original post.

Tom1977 gave it a good try but the all the formulas in the workbook return a #VALUE error.

I will fiddle around with the formulas therein to see if I can eliminate the errors.

If someone can point me in the right direction of a starting formula I'd appreciate it.

Thanks again!

Luix

since I was aiming at not having to use helper columns -see original post

.

Does the attached help? ( to me col D and E are helper columns)

Luix
Did You accepted my formulas by CTRL+SHIFT+ENTER?(without this always will be error) I have used array formulas

Just realized there are some blank cells in my actual column. So the formulas return a #VALUE error when they encounter a blank.

The following ARRAY formulas correct that problem:

=SUM(IF(ISNUMBER(SEARCH("..",B1:B19)),SUBSTITUTE(B1:B19,RIGHT(B1:B19,LEN(B1:B19)-FIND("..",B1:B19)+1),"")+0))

=SUM(IF(ISNUMBER(SEARCH("..",B1:B19)),SUBSTITUTE(B1:B19,LEFT(B1:B19,FIND("..",B1:B19)+1),"")+0))