I have try to separate the Cad Work Description by extracting the number from text using the MID().
=MID(C2;10;1)+0
When removing the C2 text; it show me #VALUE! in H3
Is there any way to solve this?
Thanks
Last edited by kotoma; 03-18-2010 at 05:30 PM.
In your sheet C3 is blank so you'll get a value error with the MID function referring to C3.....
Thanks for you replay daddylonglegs!
Yes that is right!
But is it a way to show in H3 blank or 0?
Or even an other way to formulate the whole ROW?
Thanks
Last edited by Paul; 03-11-2010 at 06:45 PM.
Not sure if this is what you mean:
=IF(C3="","",MID(C3;10;1)+0) to return a "blank" result
=IF(C3="",0,MID(C3;10;1)+0) to return a zero result
I don't know what you're trying to accomplish with the '+0' because it doesn't do anything as is. I assume you're trying to add a zero to the extracted number in which case you should be multiplying the extracted number by 10.
MID always returns a text result, +0 here is used as a "coercer" to convert a text "5" to a numeric 5......if you don't do that then a formula like =SUM(G2:H2) won't work correctly because it won't include a text formatted "5" in the sum......Originally Posted by Cutter
Hi Cutter,
Thank you for your respond!
I tryed both of your alternatives but it didn't work for me.
Well I attached the update file that will be easier to explane.
What I want is to be able to report the time I work every day, but I get paid different price per hour doing "Other job than Cad Work" also I need to write both some times.
This is the way I want to do...
by using the =(MID(C15;10;1)+0) funtion; I extract the 10th digit by changing the 5 or any number from the Cad Work 5 hours.
I just don't like to see #VALUE! if I don't type accoding to column "C" Description.
If anyone knows a solution to it, I very much apreciate it!
Thanks for taking your time!
Reg.
Kotoma
Last edited by Paul; 03-11-2010 at 06:46 PM.
That since to be right daddylonglegs!
I have just attached a XLS sample, I hope it will explaned with 3 alternatives.
Reg.
Kotoma
Cutter's suggestion should work for you but you need to change some , to ; for your version of excel, hence
=IF(C3="";"";MID(C3;10;1)+0)
Hi daddylonglegs, it does not work with my file.
I include it in this messege; in case you feel edit it to show or someone else with more experience
Hi,
Try the following;
The C3 cell in your latest spreadsheet did not contain any numbers, and so the previous proffered solution would not would as while the cell was not empty it did contain text but no numbers in the text.Code:=IF(C3="";"";IF(ISNUMBER(ABS(MID(C3;10;1))),ABS(MID(C3;10;1));"N/A"))
The formula above evaluates if the value returned by MID(C3,10,1) is a number, and if it is then returns the value, but if not returns "N/A"
The ABS part does the same thing as the +0,
A Question though: Do you have any jobs that are 10 or more hours??
Hi Jbentley,
Fist of all, thanks for your Code:
To answer to your question, yes I do! and I charge to the company different price for other jobs than Cad Work but it will not count overtime.
If I just do Cad Work only it is simple see row 4. The problem is when I do both.
See this file for example "Update04 MID.xls"
I like to have a black cell instead of #VALUE! or "N/A"
Is that possible?
Last edited by shg; 03-17-2010 at 08:56 PM. Reason: deleted spurious quote
try in H2 and copy down
=IF(OR(C2="",ISERROR(MID(C2,10,1)+0)),"",MID(C2,10,1)+0)
teylyn
Microsoft MVP - Excel
At Excelforum, you can say "Thank you!" by clicking theicon below the post.
Avoid pie charts with more than two data points. Why? See here (pdf, 559 kb). The only acceptable pie chart is here.
do you want a formula yo can drag down in col h?
Cad Work 5 hours
Meeting for concept U
Cad Work
all seem to want a different thing in col h, how do you determine what you want in h3?
Mojito connoisseur and a dabbler in Cisco
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
You could use
=IF(C2=H$1,(E2-D2-F2)*24,IF(ISERROR(MID(C2,10,1)+0),0,(TRIM(MID(C2,10,2))+0)))
in column H
If the number that is being extracted from C2 is an integer, this should prevent the Value error in the C2="" case
=(MID(C2;10;1)&".0")+0
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks