Calculating Currency with text

[ExcelRoy]

Hi Guys,

Is it possible to calculate simple figures with text

ie

2.450m
2.125m
1.475m

to form a total of 6.050

Thanks

 

[kpark91]

maybe
=LEFT(A1, 5)+0 + LEFT(B1, 5)+0 + LEFT(C1, 5)+0

 

[ExcelRoy]

Hi kpark91,

that works perfect, thanks

i have adapted to suit my needs, but it appears to be longer than it needs to be.
Is there a way to shorten the formula

="£" & LEFT(G11, 4)+0 + LEFT(G12, 4)+0 + LEFT(G13, 4)+0 +LEFT(G14, 4)+0 + LEFT(G15, 4)+0 + LEFT(G16, 4)+0+LEFT(G17, 4)+0 + LEFT(G18, 4)+0 + LEFT(G19, 4)+0+LEFT(G20, 4)+0 + LEFT(G21, 4)+0 &"m"

Thanks for your help

 

[Aladin Akyurek]

Hi Guys,

Is it possible to calculate simple figures with text

ie

2.450m
2.125m
1.475m

to form a total of 6.050

Thanks

Does...

=SUMPRODUCT(SUBSTITUTE(A2:A4,RIGHT(A2:A4),"")+0)

meet the full specs?

 

[kpark91]

Something like this?

=SUM(LEFT(G11:G21,4)+0)

Don't just enter the formula but enter with Ctrl + Shift + Enter

 

[ExcelRoy]

Thanks Aladin,

will this work with excel 2003?

 

[Aladin Akyurek]

Thanks Aladin,

will this work with excel 2003?

Yes

 

[ExcelRoy]

Many thanks guys

 

[ExcelRoy]

Hi Guys,

Thanks for your help yesterday. I have found bot solutions work well

One minor problem, if for some reason we delete the criteria the calculation shows an #VALUE error

Is there a way to show no errors? Just "" would be fine

Thanks

 

[Aladin Akyurek]

Hi Guys,

Thanks for your help yesterday. I have found bot solutions work well

One minor problem, if for some reason we delete the criteria the calculation shows an #VALUE error

Is there a way to show no errors? Just "" would be fine

Thanks

Care to elaborate on what you mean?