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!

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

<tbody>
</tbody>

 

[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!

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

<tbody>
</tbody>

 

[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:

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

<tbody>
</tbody>

 

[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!

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

<tbody>
</tbody>

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)