Quotation marks being added to formula answer

[cossie2k]

Hi Everyone,

Im hoping you can help me with this, its probably really simple to fix but I just cant see the wood for the trees.

Heres the formula.....

=IF(L29="dog",(LEFT(K29,FIND(" ",K29)-1)),((LEFT(K29,FIND(" ",K29)-1))*Q29))

For some reason, the section in bold is returning the value with quotation marks stopping me from onward processing. I don't have this issue on the back end of the formula though.

Thanks for your help
Kind regards
Alex

[TMS]

LEFT is a Text function and returns, well, text. Assuming you have two numeric values separated by a space, try this:

Please Login or Register  to view this content.

Note that you have more brackets than necessary:

Please Login or Register  to view this content.

Regards, TMS

[TMS]

Should have said, the second half of the IF formula, the FALSE element, has a multiplication which coerces a numeric value.

[cossie2k]

That's brilliant thanks TMS. The value its separating is actually time. The cell reads something like 0.5 hrs. I want to cut the " hrs" off and multiply it by a price. I cut the hrs off in other ways (text to columns for example) but its externally updated so I would rather have a live field.

[TMS]

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.