Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-02-2018, 12:32 AM
ArviLaanemets ArviLaanemets is offline Using IF ISBLANK with formula Windows 8 Using IF ISBLANK with formula Office 2016
Expert
 
Join Date: May 2017
Posts: 960
ArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant future
Default

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)
Like the formula
Code:
=IFERROR(LEFT(C2,FIND(" ",C2,1)-1),C2)
returns contents of C2 until 1st space, or the whole entry in C2, when there is no space.

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).
Reply With Quote
Reply



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
Using IF ISBLANK with formula 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
Using IF ISBLANK with formula Help with formula please. AndrewSE Excel 3 04-05-2011 08:50 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:02 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft