Extract the total value

[Ashish Batham]

Hi,

I have data base in an excel spreadsheet. The data consists of debit & credit details of companies.
At the end of the spreadsheet, there is "total value" of these credits & debits.
eg:

Cell A16 = "Total Value"
Cell B17 = $20,000 (Credit)
Cell C18 = $10,000 (Dedit)

The cell can changes for the above details, depending on the number of transactions.

Please advice, if I can extract the Amount from cells B17 & C18 automatically?

Thanks & Regards,
Ashish

 

[Luke M]

I take that to mean that the totals cell has both numbers and text in the cell, and you only want the numbers?
If so, something like this will do the trick.
=VALUE(TRIM(LEFT(SUBSTITUTE(B17," ",REPT(" ",999)),999)))

PS. That's a odd layout of your spreadsheet, if the total amounts change columns AND rows...

 

[Ashish Batham]

Thanks Luke, Will Check with this & let you know if any queries

Have a nice day,
Regards,
Ashish

 

[Ashish Batham]

Hi Luke,

Above formula I entered in C17, but value is coming as 0
eg:
Cell B17 has "Total 20000 10000"
20000 is Credit
10000 is debit
Need these in C17, D18 & E17

Thanks

 

[SirJB7]

Hi, Ashish Batham!
From your 1st post:

Cell A16 = "Total Value"
Cell B17 = $20,000 (Credit)
Cell C18 = $10,000 (Dedit)
Please advice, if I can extract the Amount from cells B17 & C18 automatically?

From your last post (less than 1 hour later):

Above formula I entered in C17, but value is coming as 0
eg:
Cell B17 has "Total 20000 10000"
20000 is Credit
10000 is debit

Am I dumb, have I misread, or do you pretend that a formula which works for the 1st cell content structure would work too for an absolutely different one? If so, please formulate your questions with the accuracy required so as to don't make people, who only want to help you, to work twice or more.
Regards!

@Luke M
Hi!
Sorry for your lose of time. And the worst part it's that this doesn't seem to be the 1st time with this OP.
Regards!

 

[Luke M]

Ashish,

This is the layout you described in last post. Please indicate what you want to appear in each labelled cell.

 

[Ashish Batham]

B to be in Cell D17

A=Total
B=20000
C=10000

 

[Luke M]

Again, it would be easier to do a Text-to-columns, but here's the formulas.
In C17:
=LEFT(B17,FIND(" ",B17)-1)
In D17:
=VALUE(MID(B17,FIND(" ",B17)+1,FIND(" ",B17,FIND(" ",B17)+1)-FIND(" ",B17)))
In E17:
=VALUE(TRIM(RIGHT(SUBSTITUTE(B17," ",REPT(" ",999)),999)))