![]() |
|
#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). |
|
|
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 |