I have entered four amounts in a single cell using Alt+Enter keys, like this:
50
600
7000
70000
Is there any way to differentiate these amounts by using char(10) and then SUM up these amount?
I have entered four amounts in a single cell using Alt+Enter keys, like this:
50
600
7000
70000
Is there any way to differentiate these amounts by using char(10) and then SUM up these amount?
Why enter them in a single cell only to want to effectively split them out?
Try this array formula
Enter in B1
***Array formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around your formula.
If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Formula:Please Login or Register to view this content.
v A B 1 50
600
7000
7000077650
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
If I use multiple columns, the sheet will become wider enough. I don't want to split my single record (related fields) into multiple rows. I want to have a one row record so that I could filter my data easily
alkey sir,Array formula ,SUM(IFERROR(--MID(SUBSTITUTE(A1,CHAR(10),REPT(" ",99)),99*(COLUMN(A:Z)-1)+1,99),"")) if use formula it will return a1...value only.May be you left to type A1:A4... otherwise formula is good.SUM(IFERROR(--MID(SUBSTITUTE(A1:A4,CHAR(10),REPT(" ",99)),99*(COLUMN(A:Z)-1)+1,99),"")).
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks