#1
|
|||
|
|||
Using IF ISBLANK with formula
I have this formula.
=IF(ISERROR(FIND(" ",C2,FIND(" ",C2,1)+1)),C2,LEFT(C2,FIND(" ",C2,FIND(" ",C2,1)+1))) & RIGHT(C2,LEN(C2)-FIND("-",C2)) If there is no data in C2 I want it to return a blank cell and not #VALUE! I have seen IF ISBLANK used and a few other methods but I can’t figure out how to apply it to my formula. Thanks |
#2
|
||||
|
||||
Hello
as this is your 26th post you should know on which forum to post. I will move it to the correct subforum
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
||||
|
||||
Quote:
=IF(C2="","",FIND..................... |
#4
|
|||
|
|||
Probably you'll have explain what you formula must do, and which kind of entry you except in C2.
Better use IFERROR()! Generally the syntax to use IFERROR() is like Code:
=IFERROR(YourExpression,ExpressionForError) Code:
=IFERROR(LEFT(C2,FIND(" ",C2,1)-1),C2) You check in your formula for 2 spaces in C2 entry, and it looks like you want to return all left from 2nd space. And it looks like you want to concatenate this with all right at character "-" in end of entry in C2. This means, that there can be at least 3 different reasons the formula can result as error: 1. There is no space in entry; 2. There is only one space in entry; There is no "-" in entry. The formula you need will depend on conditions you apply to entry in C2. When the valid entry will always have at least 2 spaces, and "-" in it at any position, you can simply use SUBSTITUTE() with LEN() to determine validity of entry (When checking for existence of double space, you can ensure the valid entry has 2 spaces at different locations. You also have to decide, is more than 2 spaces allowed. And what about having more than one "-". And probably you have to check that "-" is at right from both spaces). When the structure of valid entry is not so strictly defined, you probably need IFERROR() applied at several levels of formula (What to return when no space is present in entry, what to return, when only one space is present, what to return when no "-" is present). |
#5
|
||||
|
||||
@Arvi
Looks like in this case the IF(ISERROR syntax is the way to go The OP formula states that if the first FIND returns an error, it returns C2 If not it returns the concatenation. IFERROR will not cope with this. @14spar Try adding=IF(C2="","",IF(Iserror....
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#6
|
|||
|
|||
Hey guys, thanks for all the great input here. Sorry for originally posting this in the wrong forum. I was originally thinking that formulas might somehow be associated with Excel Programming. I first tried Marcia’s approach and it failed. After reading ArviLaanemets informative bit on how the IFError works I’m thinking it maybe failed because there are provisions in my formula for errors. I used this formula with the ISERROR provisions because it was the only one I could find that was close to what I was trying to do. I try and dissect the formulas to see just how they’re working and it seems I should stay away from the IF/IS ERROR till I have a better basic understanding of how these formals work. For now Pecoflyer’s solution has solved the problem. I had seen this =IF(C2="","".. approach used in this forum but not with the “IF” statements and when I tried it earlier apparently I had applied it incorrectly. Thanks again for the help here.
|
#7
|
|||
|
|||
With using ISERROR(), and checking both parts of your function, the possible solution may be (be careful to keep relative/absolute references correct):
1. Activate any cell in row 2 and define a dynamic Name (I assume the sheet name is Sheet1), e.g. Code:
nDynamicName1 = LEFT(Sheet1!$C2,FIND(" ",Sheet1!$C2,FIND(" ",Sheet1!$C2,1)+1)) 2. Remain in row 2 and define another dynamic Name, e.g. Code:
nDynamicName2 = RIGHT(Sheet1!$C2,LEN(Sheet1!$C2)-FIND("-",Sheet1!$C2)) To return empty string whenever C2 is empty, C2 whenever nDynamicName1 or nDynamicName2 returns an error, and your formula result otherwise, the formula in any column of row 2 will be Code:
="" & IF(OR(ISERROR(nDynamicName1),ISERROR(nDynamicName2)),$C2,nDynamicName1 & nDynamicName2) |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Formula Copy Row 2 Row But Next Column In Formula From Another Tab | TimG | Excel | 3 | 04-16-2018 09:20 PM |
Possible to use an existing vlookup formula to also insert correct info and trigger a SUM formula | innkeeper9 | Excel | 2 | 09-13-2016 08:59 PM |
Help With a Formula | OTPM | Excel | 3 | 05-26-2016 03:42 AM |
Need help with dragging a formula and changing a reference column as I drag the formula. | LupeB | Excel | 1 | 10-22-2015 03:02 PM |
Help with formula please. | AndrewSE | Excel | 3 | 04-05-2011 08:50 PM |