Embarrassed to ask this question ....

[Bullpen]

Hey everyone,

I hate to ask this question because the answer is going to be really easy. I need to separate the direction from a list of addresses. For example, in cell A1 I have 'W Main St', and in cell A2 I have 'First Ave. I need to write a formula in cell B1 that will pull out the 'W' and that in cell B2 it will know that there is no direction (i.e. no N, W, S, E).

How do I write a formula that will tell excel that if the second character in a text string is a space, then return the first character, and if the second character in a text string in NOT a space, then return nothing?

Thanks for your help! Feel free to ask any follow up questions!

John

 

[Jonmo1]

Welcome to the board...

Try

=IF(MID(A1,2,1)=" ",LEFT(A1,1),"")

Hope that helps.

 

[T. Valko]

Hey everyone,

I hate to ask this question because the answer is going to be really easy. I need to separate the direction from a list of addresses. For example, in cell A1 I have 'W Main St', and in cell A2 I have 'First Ave. I need to write a formula in cell B1 that will pull out the 'W' and that in cell B2 it will know that there is no direction (i.e. no N, W, S, E).

How do I write a formula that will tell excel that if the second character in a text string is a space, then return the first character, and if the second character in a text string in NOT a space, then return nothing?

Thanks for your help! Feel free to ask any follow up questions!

John

Maybe something like this...

=IF(MID(A2,2,1)=" ",LEFT(A2),"")

 

[VoG]

Try this

Excel Workbook
	A	B
1	W Main Street	W
2	First Ave
Sheet6

 

[Bullpen]

Thank you so much for all of your answers. VOG .. you nailed it!

Thank you again! You guys are incredible!

John