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]

 

[RCBricker]

try these for your city and state.

***note change cell references to match your data

city column

=IF(ISERROR(LEFT(C2,FIND(",",C2)-1)),C2,LEFT(C2,FIND(",",C2)-1))

state column

=IF(ISERROR(MID(C2,FIND(",",C2)+2,2)),C2,MID(C2,FIND(",",C2)+2,2))

rich

 

[ukmikeb]

Hi

Welcome to the MrExcel Forum

Try in D9 for the left part (City) :-

=IFERROR(LEFT(C9,FIND(",",C9)-1),C9)

and in E9 for the right part (State) :-

=IFERROR(RIGHT(C9,LEN(C9)-(FIND(", ",C9)+2)),"")

hth

 

[colgor]

Thanks Rich, that worked a treat in terms of a solution, you're a star! There were a couple of records, below, where the format was not the same so did not work but that does not matter for my purposes whilst I am learning. The formula is a little advanced for me at this stage, for we are asked to go look for data and analyse if for an assignment, so can't use it unless I understand how and why I did it? I'll need to go googling or youtubing the ISERROR command so I can justify its inclusion. Thanks again!

[TABLE="width: 983"]
<tbody>[TR]
[TD]Airline[/TD]
[TD]Flight[/TD]
[TD]From[/TD]
[TD]City (cell D8)[/TD]
[TD]State/Country (cell E8)[/TD]
[TD]Sch Time[/TD]
[TD]Gate[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]Southwest[/TD]
[TD]2875[/TD]
[TD]Denver, CO[/TD]
[TD]Denver[/TD]
[TD]CO[/TD]
[TD]12:45 PM[/TD]
[TD]21[/TD]
[TD]On Time[/TD]
[/TR]
[TR]
[TD]United[/TD]
[TD]831[/TD]
[TD]Denver, CO[/TD]
[TD]Denver[/TD]
[TD]CO[/TD]
[TD]1:10 PM[/TD]
[TD]12[/TD]
[TD]On Time[/TD]
[/TR]
[TR]
[TD]Southwest[/TD]
[TD]1677[/TD]
[TD]Greenville, SC[/TD]
[TD]Greenville[/TD]
[TD]SC[/TD]
[TD]11:50 AM[/TD]
[TD]24[/TD]
[TD]On Time[/TD]
[/TR]
[TR]
[TD]Volaris[/TD]
[TD]930[/TD]
[TD]Guadalajara[/TD]
[TD]Guadalajara[/TD]
[TD]Guadalajara[/TD]
[TD]12:05 PM[/TD]
[TD]16[/TD]
[TD]On Time[/TD]
[/TR]
[TR]
[TD]All Nippon[/TD]
[TD]172[/TD]
[TD]Tokyo-Narita[/TD]
[TD]Tokyo-Narita[/TD]
[TD]Tokyo-Narita[/TD]
[TD]11:00 AM[/TD]
[TD]15[/TD]
[TD]On Time[/TD]
[/TR]
[TR]
[TD]Southwest[/TD]
[TD]2834[/TD]
[TD]Tucson, AZ[/TD]
[TD]Tucson[/TD]
[TD]AZ[/TD]
[TD]9:10 AM[/TD]
[TD]19[/TD]
[TD]Arrived 09:07 AM[/TD]
[/TR]
</tbody>[/TABLE]

 

[colgor]

Another star, thank you Mike, I'm going to like this website and the helpful folk I find here!
Your solution did not quite work out for the state/E9, see below:

[TABLE="width: 945"]
<tbody>[TR]
[TD]Airline
[/TD]
[TD]Flight
[/TD]
[TD]From
[/TD]
[TD]City (cell D8)
[/TD]
[TD]State/Country (cell E8)
[/TD]
[TD]Sch Time
[/TD]
[TD]Gate
[/TD]
[TD]Status
[/TD]
[/TR]
[TR]
[TD]Southwest
[/TD]
[TD]805
[/TD]
[TD]Albuquerque, NM
[/TD]
[TD]Albuquerque
[/TD]
[TD]M
[/TD]
[TD]12:40 PM
[/TD]
[TD]23
[/TD]
[TD]On Time
[/TD]
[/TR]
[TR]
[TD]Southwest
[/TD]
[TD]3109
[/TD]
[TD]Austin, TX
[/TD]
[TD]Austin
[/TD]
[TD]X
[/TD]
[TD]8:50 AM
[/TD]
[TD]21
[/TD]
[TD]Arrived 08:45 AM
[/TD]
[/TR]
[TR]
[TD]Alaska
[/TD]
[TD]2403
[/TD]
[TD]Boise, Idaho
[/TD]
[TD]Boise
[/TD]
[TD]daho
[/TD]
[TD]10:00 AM
[/TD]
[TD]28
[/TD]
[TD]Arrived 09:53 AM
[/TD]
[/TR]
[TR]
[TD]Southwest
[/TD]
[TD]2812
[/TD]
[TD]Burbank, CA
[/TD]
[TD]Burbank
[/TD]
[TD]A
[/TD]
[TD]8:20 AM
[/TD]
[TD]20
[/TD]
[TD]Arrived 08:15 AM
[/TD]
[/TR]
[TR]
[TD]Southwest
[/TD]
[TD]729
[/TD]
[TD]Burbank, CA
[/TD]
[TD]Burbank
[/TD]
[TD]A
[/TD]
[TD]9:35 AM
[/TD]
[TD]20
[/TD]
[TD]Arrived 09:25 AM
[/TD]
[/TR]
[TR]
[TD]Southwest
[/TD]
[TD]1091
[/TD]
[TD]Burbank, CA
[/TD]
[TD]Burbank
[/TD]
[TD]A
[/TD]
[TD]11:30 AM
[/TD]
[TD]22
[/TD]
[TD]On Time
[/TD]
[/TR]
[TR]
[TD]Southwest
[/TD]
[TD]2226
[/TD]
[TD]Chicago, IL-Midway
[/TD]
[TD]Chicago
[/TD]
[TD]L-Midway
[/TD]
[TD]12:45 PM
[/TD]
[TD]22
[/TD]
[TD]On Time
[/TD]
[/TR]
[TR]
[TD]American
[/TD]
[TD]2452
[/TD]
[TD]Chicago, IL-O'Hare
[/TD]
[TD]Chicago
[/TD]
[TD]L-O'Hare
[/TD]
[TD]1:14 PM
[/TD]
[TD]9
[/TD]
[TD]Now 12:56 PM
[/TD]
[/TR]
[TR]
[TD]Southwest
[/TD]
[TD]421
[/TD]
[TD]Dallas, TX-Love Field
[/TD]
[TD]Dallas
[/TD]
[TD]X-Love Field
[/TD]
[TD]12:20 PM
[/TD]
[TD]20
[/TD]
[TD]On Time
[/TD]
[/TR]
</tbody>[/TABLE]

 

[ukmikeb]

Hi

Sorry about the error, here is the correction :-

=IFERROR(RIGHT(C9,LEN(C9)-(FIND(", ",C9)+2)+1),"")

Thanks for the feedback.

Good luck with your project.

 

[colgor]

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!

 

[Snakehips]

colgor,

I know that you have a solution from Mike but for educational purposes here is another way that you could do it....

Excel 2007
	A	B	C	D	E
8	Airline	Flight	From	City (cell D8)	State/Country (cell E8)
9	Southwest	805	Albuquerque, NM	Albuquerque	NM
10	Southwest	3109	Austin, TX	Austin	TX
11	Alaska	2403	Boise, Idaho	Boise	Idaho
Sheet1

Cell Formulas
Range	Formula
D9	=TRIM((LEFT(SUBSTITUTE(C9,",",REPT(" ",99)),99)))
E9	=TRIM(SUBSTITUTE(SUBSTITUTE(C9,D9,""),",",""))

 

[RCBricker]

Thanks Rich, that worked a treat in terms of a solution, you're a star! There were a couple of records, below, where the format was not the same so did not work but that does not matter for my purposes whilst I am learning. The formula is a little advanced for me at this stage, for we are asked to go look for data and analyse if for an assignment, so can't use it unless I understand how and why I did it? I'll need to go googling or youtubing the ISERROR command so I can justify its inclusion. Thanks again!

[TABLE="width: 983"]
<tbody>[TR]
[TD]Airline[/TD]
[TD]Flight[/TD]
[TD]From[/TD]
[TD]City (cell D8)[/TD]
[TD]State/Country (cell E8)[/TD]
[TD]Sch Time[/TD]
[TD]Gate[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]Southwest[/TD]
[TD]2875[/TD]
[TD]Denver, CO[/TD]
[TD]Denver[/TD]
[TD]CO[/TD]
[TD]12:45 PM[/TD]
[TD]21[/TD]
[TD]On Time[/TD]
[/TR]
[TR]
[TD]United[/TD]
[TD]831[/TD]
[TD]Denver, CO[/TD]
[TD]Denver[/TD]
[TD]CO[/TD]
[TD]1:10 PM[/TD]
[TD]12[/TD]
[TD]On Time[/TD]
[/TR]
[TR]
[TD]Southwest[/TD]
[TD]1677[/TD]
[TD]Greenville, SC[/TD]
[TD]Greenville[/TD]
[TD]SC[/TD]
[TD]11:50 AM[/TD]
[TD]24[/TD]
[TD]On Time[/TD]
[/TR]
[TR]
[TD]Volaris[/TD]
[TD]930[/TD]
[TD]Guadalajara[/TD]
[TD]Guadalajara[/TD]
[TD]Guadalajara[/TD]
[TD]12:05 PM[/TD]
[TD]16[/TD]
[TD]On Time[/TD]
[/TR]
[TR]
[TD]All Nippon[/TD]
[TD]172[/TD]
[TD]Tokyo-Narita[/TD]
[TD]Tokyo-Narita[/TD]
[TD]Tokyo-Narita[/TD]
[TD]11:00 AM[/TD]
[TD]15[/TD]
[TD]On Time[/TD]
[/TR]
[TR]
[TD]Southwest[/TD]
[TD]2834[/TD]
[TD]Tucson, AZ[/TD]
[TD]Tucson[/TD]
[TD]AZ[/TD]
[TD]9:10 AM[/TD]
[TD]19[/TD]
[TD]Arrived 09:07 AM[/TD]
[/TR]
</tbody>[/TABLE]

The iserror is a simple command that looks at a formula to determine if it returns an error. If so it gives the option of what to return rather than the error itself. For example instead of a #value error you could have it return a zero (0). Placing the iserror in an IF statement simply always you to manage errors if they occur. So in this case if the formula returns an error I had it return the cell in column C. otherwise just run the formula itself since it will not return an error.

HTH

Rich

 

[bosco_yip]

1] City, D9 :

=LEFT(C9,FIND(",",C9&",")-1)

2] State, E9 :

=MID(C9,FIND(",",C9&",")+2,250)