Formula problem when trying to do a nested FIND within LEFT function

[colgor]

Hi, I am new on this forum, my first post, and hope you can help? I've just started learning about LEFT, RIGHT FIND, MID and LEN. I am practising nesting FIND within the LEFT function. I have pulled some data from an airline website at random, then tried to use FIND to look for a "space" in order to break up the string into two different columns of information. I find that where the information differs, or is not there after the comma in column C, that the result will not return just the first part of the string (which is what I am trying to to). I hope for some tips and that the following will help you understand my issue? Many thanks for any assistance!

[TABLE="width: 1105"]
<tbody>[TR]
[TD="colspan: 10"]Mineta San Jose International Airport | 1701 Airport Blvd. | San Jose, CA 95110 | 408.392.3600 [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]I used formula =LEFT(C9, FIND(" ",C9)-2) in cell D9 then pasted down.[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]Hit problems when the format after the comma differs, either words[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]cut off or returns a #value. Can anyone give me a steer on how best[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]to amend the formula?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]UPDATED: Tue Sep-15-15 10:25:05 AM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Airline[/TD]
[TD]Flight[/TD]
[TD]From (cell C8)[/TD]
[TD]City (cell D8)[/TD]
[TD]State/Country (cell E8)[/TD]
[TD]Sch Time[/TD]
[TD]Gate[/TD]
[TD]Status[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Southwest[/TD]
[TD]805[/TD]
[TD]Albuquerque, NM[/TD]
[TD]Albuquerque[/TD]
[TD][/TD]
[TD]12:40 PM[/TD]
[TD]23[/TD]
[TD]On Time[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Southwest[/TD]
[TD]3109[/TD]
[TD]Austin, TX[/TD]
[TD]Austin[/TD]
[TD][/TD]
[TD]8:50 AM[/TD]
[TD]21[/TD]
[TD]Arrived 08:45 AM[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alaska[/TD]
[TD]2403[/TD]
[TD]Boise, Idaho[/TD]
[TD]Boise[/TD]
[TD][/TD]
[TD]10:00 AM[/TD]
[TD]28[/TD]
[TD]Arrived 09:53 AM[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Southwest[/TD]
[TD]2812[/TD]
[TD]Burbank, CA[/TD]
[TD]Burbank[/TD]
[TD][/TD]
[TD]8:20 AM[/TD]
[TD]20[/TD]
[TD]Arrived 08:15 AM[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Southwest[/TD]
[TD]729[/TD]
[TD]Burbank, CA[/TD]
[TD]Burbank[/TD]
[TD][/TD]
[TD]9:35 AM[/TD]
[TD]20[/TD]
[TD]Arrived 09:25 AM[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Southwest[/TD]
[TD]1091[/TD]
[TD]Burbank, CA[/TD]
[TD]Burbank[/TD]
[TD][/TD]
[TD]11:30 AM[/TD]
[TD]22[/TD]
[TD]On Time[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Southwest[/TD]
[TD]2226[/TD]
[TD]Chicago, IL-Midway[/TD]
[TD]Chicago[/TD]
[TD][/TD]
[TD]12:45 PM[/TD]
[TD]22[/TD]
[TD]On Time[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]American[/TD]
[TD]2452[/TD]
[TD]Chicago, IL-O'Hare[/TD]
[TD]Chicago[/TD]
[TD][/TD]
[TD]1:14 PM[/TD]
[TD]9[/TD]
[TD]Now 12:56 PM[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Southwest[/TD]
[TD]421[/TD]
[TD]Dallas, TX-Love Field[/TD]
[TD]Dallas[/TD]
[TD][/TD]
[TD]12:20 PM[/TD]
[TD]20[/TD]
[TD]On Time[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]American[/TD]
[TD]2232[/TD]
[TD]Dallas/Fort Worth, TX[/TD]
[TD]Dallas/For[/TD]
[TD][/TD]
[TD]10:51 AM[/TD]
[TD]9[/TD]
[TD]On Time[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]American[/TD]
[TD]89[/TD]
[TD]Dallas/Fort Worth, TX[/TD]
[TD]Dallas/For[/TD]
[TD][/TD]
[TD]12:24 PM[/TD]
[TD]10[/TD]
[TD]Now 12:34 PM[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]United[/TD]
[TD]5592[/TD]
[TD]Denver, CO[/TD]
[TD]Denver[/TD]
[TD][/TD]
[TD]9:37 AM[/TD]
[TD]13[/TD]
[TD]Arrived 09:39 AM[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Southwest[/TD]
[TD]3198[/TD]
[TD]Denver, CO[/TD]
[TD]Denver[/TD]
[TD][/TD]
[TD]10:15 AM[/TD]
[TD]22[/TD]
[TD]Arrived 10:05 AM[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Southwest[/TD]
[TD]1091[/TD]
[TD]Denver, CO[/TD]
[TD]Denver[/TD]
[TD][/TD]
[TD]11:30 AM[/TD]
[TD]22[/TD]
[TD]On Time[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Southwest[/TD]
[TD]2875[/TD]
[TD]Denver, CO[/TD]
[TD]Denver[/TD]
[TD][/TD]
[TD]12:45 PM[/TD]
[TD]21[/TD]
[TD]On Time[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]United[/TD]
[TD]831[/TD]
[TD]Denver, CO[/TD]
[TD]Denver[/TD]
[TD][/TD]
[TD]1:10 PM[/TD]
[TD]12[/TD]
[TD]On Time[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Southwest[/TD]
[TD]1677[/TD]
[TD]Greenville, SC[/TD]
[TD]Greenville[/TD]
[TD][/TD]
[TD]11:50 AM[/TD]
[TD]24[/TD]
[TD]On Time[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Volaris[/TD]
[TD]930[/TD]
[TD]Guadalajara[/TD]
[TD]#VALUE![/TD]
[TD][/TD]
[TD]12:05 PM[/TD]
[TD]16[/TD]
[TD]On Time[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Southwest[/TD]
[TD]508[/TD]
[TD]Kansas City, MO[/TD]
[TD]Kansa[/TD]
[TD][/TD]
[TD]11:10 AM[/TD]
[TD]20[/TD]
[TD]On Time[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Southwest[/TD]
[TD]2918[/TD]
[TD]Las Vegas, NV[/TD]
[TD]La[/TD]
[TD][/TD]
[TD]7:35 AM[/TD]
[TD]24[/TD]
[TD]Arrived 07:25 AM[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Southwest[/TD]
[TD]1905[/TD]
[TD]Las Vegas, NV[/TD]
[TD]La[/TD]
[TD][/TD]
[TD]9:05 AM[/TD]
[TD]24[/TD]
[TD]Arrived 08:55 AM[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[ukmikeb]

Please don't apologise Mike! Feels a bit like a Spaniard apologising to me on holiday for his poor English, when I as the lazy brit cannot speak a word of Spanish! Your new code worked a treat and left spaces where appropriate, for example in the State with Guadalajara and Tokyo. Now I must get googling IFERROR to deconstruct your formulas to properly understand. What I have found is that for a quick solution to a problem at work this site is fantastic, you are too helpful to me whilst I am learning ... aka me cheating!

IFERROR is used in Excel version 2007 and later to trap errors. It is similar to ISERROR, which Rich (@RCBricker) used in his solution, which was for Excel version 2003 and prior. The beauty of IFERROR is that you only need to state your formula once whereas in using ISERROR you need to quote the formula or part thereof twice. It is a common mistake to quote the whole formula with ISERROR when quoting the part likely to cause an error will suffice, like the MATCH part of an INDEX/MATCH formula.

@Snakehips I see you have been to RR's school of Substitution.
An alternative to your formula in E9 could be :-

=TRIM((RIGHT(SUBSTITUTE(C9,",",REPT(" ",99)),99)))

 

[Snakehips]

@Snakehips I see you have been to RR's school of Substitution.

Reckon you could right there Mike and very useful it is on occasion.

An alternative to your formula in E9 could be :-

=TRIM((RIGHT(SUBSTITUTE(C9,",",REPT(" ",99)),99)))

I think this returns the odd error with e.g. Guadalajara. Returns Guadalajara rather than blank.

 

[RCBricker]

Reckon you could right there Mike and very useful it is on occasion.



I think this returns the odd error with e.g. Guadalajara. Returns Guadalajara rather than blank.

I have seen this substitute before, and since you two brought it up...

Why do this? What purpose or advantage is there to adding so many spaces?

rich

 

[ukmikeb]

I have seen this substitute before, and since you two brought it up...

Why do this? What purpose or advantage is there to adding so many spaces?

With the OP's data there is a focus point of the comma.

The point of adding the spaces is to create a larger boundary of spaces between the parts of the data string so that separating them you can use the basic
LEFT and RIGHT functions allied with the TRIM function to remove superfluous spaces.

In essence it is a lazier way of breaking up the string because you don't have to concern yourself with the position of the comma and the space after it in this case.

The only concern one has in using this method is that you allow sufficient margin to include the largest string to the left or right.

I hope this clarifies the use of the method for you.

@Snakehips Thanks, I see your point which can be overcome by adding a ghost comma to C9 in this instance.

 

[RCBricker]

Thanks mike!

I see the advantage to adding the spaces (as you said makes it easier than having to worry about the actual spaces in the text).

Rich