Seems easy enough, HA!


Cell A1/A2 =(multiple invoices on the same check)

_______________
$19.95
$0 - refused
$1163.22
$489.88
_______________

$1331.75
$37.90
$37.90
$130.29
_______________


Cell B1/B2 =(calculate sum for A1/A2)
_______________

$1673.05

_______________

$1537.84

_______________



Thank you, big(ger) brains!

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

In article <tl7m8q$2tqfo$1@dont-email.me>, leonard hofstatder wrote...

Seems easy enough, HA!

Cell A1/A2 =(multiple invoices on the same check)

_______________
$19.95
$0 - refused
$1163.22
$489.88
_______________

$1331.75
$37.90
$37.90
$130.29
_______________

Cell B1/B2 =(calculate sum for A1/A2)
_______________

$1673.05

_______________

$1537.84

_______________

Thank you, big(ger) brains!

If I understand this correctly (and you haven't really posted a clear question about a clearly-described situation) you have cells containing string values which a human can recognise as dollar values (one with a text comment) separated by newlines. To Excel that's just a string of characters. Attempts to impose a structure on it are hampered by the possible inclusion of comments, which make the data "dirty".

The best advice I can offer is to go back to the source of the data and tweak that until it's producing a format you can work with in Excel. There's no excuse for crap like that. Violence is generally justifiable here.

If it's produced by your boss's girlfriend, then you have to parse these strings. There's a skill to that.

I've never (yet) found time to learn Powershell, but if (as seems likely) it's as capable as the Unix shell utilities then it's conceivable (note I didn't say "possible") that you could fashion a pre-processor utility to do this.

If you wanted to stay within Excel, then you could write the necessary functionality in VBA. In principle I could do it, but not quickly, and I'd need a big sample of data to make it anything like reliable.

Now, versions of Excel since 2013 have "Power Query" available. I've only scratched the surface of that, but it can do some powerful things. It's conceivable that PQ can handle this: its purpose is to import and transform data.

But the bottom line: as country folk might sometimes say, when asked for directions: "if you want to get to there, you'd best not start from here..."

--

Phil, London

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

In article <tl823d$2ukfm$1@dont-email.me>, leonard hofstatder wrote...

On 11/18/2022 5:18 AM, Philip Herlihy wrote:

In article <tl7m8q$2tqfo$1@dont-email.me>, leonard hofstatder wrote...

Seems easy enough, HA!


Cell A1/A2 =(multiple invoices on the same check)

_______________
$19.95
$0 - refused
$1163.22
$489.88
_______________

$1331.75
$37.90
$37.90
$130.29
_______________


Cell B1/B2 =(calculate sum for A1/A2)
_______________

$1673.05

_______________

$1537.84

_______________



Thank you, big(ger) brains!

If I understand this correctly (and you haven't really posted a clear question
about a clearly-described situation) you have cells containing string values
which a human can recognise as dollar values (one with a text comment) separated by newlines. To Excel that's just a string of characters. Attempts
to impose a structure on it are hampered by the possible inclusion of comments,
which make the data "dirty".

The best advice I can offer is to go back to the source of the data and tweak
that until it's producing a format you can work with in Excel. There's no excuse for crap like that. Violence is generally justifiable here.

If it's produced by your boss's girlfriend, then you have to parse these strings. There's a skill to that.

I've never (yet) found time to learn Powershell, but if (as seems likely) it's
as capable as the Unix shell utilities then it's conceivable (note I didn't say
"possible") that you could fashion a pre-processor utility to do this.

If you wanted to stay within Excel, then you could write the necessary functionality in VBA. In principle I could do it, but not quickly, and I'd need a big sample of data to make it anything like reliable.

Now, versions of Excel since 2013 have "Power Query" available. I've only scratched the surface of that, but it can do some powerful things. It's conceivable that PQ can handle this: its purpose is to import and transform
data.

But the bottom line: as country folk might sometimes say, when asked for directions: "if you want to get to there, you'd best not start from here..."

Makes sense - seems I need to enter single cell then merge the "total"
cell. Just thought I may be overlooking a simple formula to get my
intended result. Thanks.

Just so. It's a useful principle to think independently about data and layout/format/presentation. Modern web design sets out in HTML what items on the page are, and then CSS (often in a separate linked file) sets out things like size, colour, even position on the page. Always get the data neat and clean first!

(You may have been thinking about the "Transpose" function, but that works on ranges of cells, not at the cell level.)

--

Phil, London

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 11/18/2022 5:18 AM, Philip Herlihy wrote:

In article <tl7m8q$2tqfo$1@dont-email.me>, leonard hofstatder wrote...

Seems easy enough, HA!


Cell A1/A2 =(multiple invoices on the same check)

_______________
$19.95
$0 - refused
$1163.22
$489.88
_______________

$1331.75
$37.90
$37.90
$130.29
_______________


Cell B1/B2 =(calculate sum for A1/A2)
_______________

$1673.05

_______________

$1537.84

_______________



Thank you, big(ger) brains!

If I understand this correctly (and you haven't really posted a clear question
about a clearly-described situation) you have cells containing string values which a human can recognise as dollar values (one with a text comment) separated by newlines. To Excel that's just a string of characters. Attempts
to impose a structure on it are hampered by the possible inclusion of comments,
which make the data "dirty".

The best advice I can offer is to go back to the source of the data and tweak that until it's producing a format you can work with in Excel. There's no excuse for crap like that. Violence is generally justifiable here.

If it's produced by your boss's girlfriend, then you have to parse these strings. There's a skill to that.

I've never (yet) found time to learn Powershell, but if (as seems likely) it's
as capable as the Unix shell utilities then it's conceivable (note I didn't say
"possible") that you could fashion a pre-processor utility to do this.

If you wanted to stay within Excel, then you could write the necessary functionality in VBA. In principle I could do it, but not quickly, and I'd need a big sample of data to make it anything like reliable.

Now, versions of Excel since 2013 have "Power Query" available. I've only scratched the surface of that, but it can do some powerful things. It's conceivable that PQ can handle this: its purpose is to import and transform data.

But the bottom line: as country folk might sometimes say, when asked for directions: "if you want to get to there, you'd best not start from here..."

Makes sense - seems I need to enter single cell then merge the "total"
cell. Just thought I may be overlooking a simple formula to get my
intended result. Thanks.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 11/18/2022 8:42 AM, Claus Busch wrote:

=SUM(IFERROR(--MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"$",)," -refused",),CHAR(10),REPT(" ",99)),ROW(1:99)*99-98,99),0))

Thank you, Claus

No go, to simplify I can remove the "refused" and $ format, just enter
numbers and just format B1 for $ - it would then present as:

19.95
0.00
1163.22
489.88

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi,

Am Fri, 18 Nov 2022 04:20:12 -0600 schrieb leonard hofstatder:

Seems easy enough, HA!

Cell A1/A2 =(multiple invoices on the same check)

_______________
$19.95
$0 - refused
$1163.22
$489.88
_______________

$1331.75
$37.90
$37.90
$130.29
_______________

Cell B1/B2 =(calculate sum for A1/A2)

for the values in A1 try: =SUM(IFERROR(--MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"$",)," -refused",),CHAR(10),REPT(" ",99)),ROW(1:99)*99-98,99),0))


Regards
Claus B.
--
Windows11
Microsoft 365 for business

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi Leonhard.

Am Fri, 18 Nov 2022 09:09:16 -0600 schrieb leonard hofstatder:

On 11/18/2022 8:42 AM, Claus Busch wrote:

=SUM(IFERROR(--MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"$",)," -refused",),CHAR(10),REPT(" ",99)),ROW(1:99)*99-98,99),0))

No go, to simplify I can remove the "refused" and $ format, just enter numbers and just format B1 for $ - it would then present as:

19.95
0.00
1163.22
489.88

then try:
=SUM(IFERROR(--MID(SUBSTITUTE(A1,CHAR(10),REPT(" ",99)),ROW($1:$99)*99-98,99),0))


Regards
Claus B.
--
Windows11
Microsoft 365 for business

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi Leonhard,

Am Fri, 18 Nov 2022 10:18:52 -0600 schrieb leonard hofstatder:

On 11/18/2022 9:39 AM, Claus Busch wrote:

=SUM(IFERROR(--MID(SUBSTITUTE(A1,CHAR(10),REPT(" ",99)),ROW($1:$99)*99-98,99),0))

When I enter more than 1 number the sum is "0".

I have a german Excel and so I must change the decimal separator to
comma.
Have a look:
https://1drv.ms/x/s!AqMiGBK2qniTgfMpTfPh4lvG2vLw_w?e=gl2k4y


Regards
Claus B.
--
Windows11
Microsoft 365 for business

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 11/18/2022 9:39 AM, Claus Busch wrote:

=SUM(IFERROR(--MID(SUBSTITUTE(A1,CHAR(10),REPT(" ",99)),ROW($1:$99)*99-98,99),0))

When I enter more than 1 number the sum is "0".

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)