Formula Question involving replacement.

[steve meier]

Hi,

I have a particular problem that i wouldn't think would be too difficult but is escaping me for some reason.

Here's what i'm trying to do.

I have several walking routes that are labeled A through H

Each walking route has a different distance. .18 miles, 1.45 miles, etc...

What i would like to do is be able to enter what routes are walked in a day and have it display the milage in a total cell.

Example

A person walked routes A twice, B and C once today.
They walked a total of 5.64 miles.

_______ __Total____
| ABAC | 5.64 |
-------- ------------


What might the formula be?

Thanks for the help, let me know if i need to provide further information.

[JBeaucaire]

This formula will tell you how many time "A" appears in cell A1.

=LEN(A1)-LEN(SUBSTITUTE(A1,"A",""))

Now you can use that answer to multiply off a table or a set of values

=(LEN(A1)-LEN(SUBSTITUTE(A1,"A","")))*0.18

Now you can string several of those together, I suppose.

=((LEN(A1)-LEN(SUBSTITUTE(A1,"A","")))*0.18)+((LEN(A1)-LEN(SUBSTITUTE(A1,"B","")))*1.45)

...etc

[DonkeyOte]

An extension of JB's post.

If we assume that A1:B9 holds the following:

Please Login or Register  to view this content.

Then we can have say the following A11:C13 (row 11 = headers)

Please Login or Register  to view this content.

Where the formula in C12 is:

Please Login or Register  to view this content.

The above formula can be copied down... as you alter the string of routes walked in column B so the miles in column C will recalculate... note it assumes each route is to be denoted by a singular character - if that changed (eg route "AA" so the approach would need to be altered)

[steve meier]

Thanks guys!

That worked perfectly. I appreciate your time and knowledge.

[e4excel]

Hey DonkeyOte,

You are truly amazing man, genius..I was actually having a similar requirement and this solution just fell in place at the right time..
Simple compact yet robust...
I needed to tag the prices for various Models assembled with various parts each having a cost to get the final cost..
I think I can do some modification based on your code...

JB's code was good as well to set the foundation ...

Amazing...!