Using a cell w/Alpha numeric characters in mulplication formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MAJ0116 - 09 Feb 2007 22:01 GMT

Trying to create multple rows with each showing
[14.3 hrs @ $185/hr] (each row different hours and $rate)

It's necessary for us to have the brackets showing, but a cell with [14.3 in
it is not recognized as a number - same thing applies to the $185/hr

Appreciate any assist

Reply to this Message

Elkar - 09 Feb 2007 23:24 GMT

Here's a couple formulas to extract the hours and dollars from your data.

=VALUE(MID(A16,2,FIND(" ",A16)-2))

=VALUE(MID(A16,FIND("$",A16)+1,FIND("/",A16)-FIND("$",A16)-1))

Is that something you can work with?

HTH,
Elkar

> Trying to create multple rows with each showing
> [14.3 hrs @ $185/hr] (each row different hours and $rate)
[quoted text clipped - 3 lines]
>
> Appreciate any assist

Reply to this Message

Gord Dibben - 09 Feb 2007 23:34 GMT

With 14.3 in A1 and 185 in B1 enter in C1

=TEXT("["&A1&" hrs @ "&B1,"$##.00")&" /hr]"

Gord Dibben MS Excel MVP

>Trying to create multple rows with each showing
>[14.3 hrs @ $185/hr] (each row different hours and $rate)
[quoted text clipped - 3 lines]
>
>Appreciate any assist

Reply to this Message

Harlan Grove - 10 Feb 2007 01:06 GMT

Gord Dibben <gorddibbATshawDOTca> wrote...

>With 14.3 in A1 and 185 in B1 enter in C1
>
>=TEXT("["&A1&" hrs @ "&B1,"$##.00")&" /hr]"

...

This returns

[14.3 hrs @ 185 /hr]

but so would

="["&A1&" hrs @ "&B1&" /hr]"

and closer to the OP's specs,

="["&A1&" hrs @ "&B1&"/hr]"

Why bother with the TEXT call?

Reply to this Message

Harlan Grove - 10 Feb 2007 01:07 GMT

"Harlan Grove" <hrln...@aol.com> wrote...
...

>and closer to the OP's specs,
>
>="["&A1&" hrs @ "&B1&"/hr]"

...

Make that

="["&A1&" hrs @ $"&B1&"/hr]"

Reply to this Message