Hello,
I need a little help with my formula.
I have a labor category that is numeric and text. I want to take the numeric portion and change it.
If the numbers are 1.1 to 1.9, then I want to create another number in a column as 101 to 109.
If the numbers are 1.10 to 1.19, then I want the number to be 110 to 119.
Here is the formula I entered:
=IF(LEFT(A12,4)>=0,LEFT(A12,1)&MID(A12,3,2),LEFT(A12,1)&0&MID(A12,3,1))
It is not working because it see's the space as 0. See attached spreadsheet.
You havent said whats meant to happen if none of those values (1.1-1.19)
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
Try
=LEFT(A3,1)&TEXT(MID(A3,3,2),"00")
Try this to see if you get the desired output....
In B3
and then copy down.
Please Login or Register to view this content.
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
@Jonmo1
Very good logic. Neat & Short.
Does not work
I tried both "Jonmo1" and "sktneer".
Jonmo1 - did you mean to enter like this?
=IF(TEXT(MID(A3,4,2),"00"),LEFT(A3,1)&0&MID(A3,3,1),LEFT(A3,1)&MID(A3,3,2))
This gives me "#Value!"
Sktneer
Your formula you typed contains an error and highlights the first "." in the formula.
I'm not that good with Excel. I haven't used substitute and find too much.
Thanks.
Last edited by kunjanee; 01-29-2015 at 12:42 PM.
Works for me.
Can you elaborate on "Does not work" ?
Do you get an error? What error?
Do you get the wrong result? What result DO you get, and what result did you expect?
In this example book, which cell(s) did not produce your desired result?
EFKunjanee.xlsx
Ok...sorry.I must have entered it wrong the first time I tried.
"Jonmo1" - Your formula works for me. =LEFT(A3,1)&TEXT(MID(A3,3,2),"00")
Thanks for all your help!
Last edited by kunjanee; 01-29-2015 at 12:13 PM.
You're welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks