Another Parsing address question

[HSL]

I was able to figure out how to parse out the house no. from an address but
am stuck on how to treat those addresses that are missing a house no.

For example,
if the address shows up as 123 Main Street, then the formula results in 123
- just as I want it.

however, if the address shows up as just "Main Street", then the formula
results in "Main". I just want it to result in null or blank.

Is there a way to get around this?

 

[T. Valko]

Assuming that the cell entry is:

123 Main Street

You can test the first character to see if it's a number. If it's number
extract the number to the first space character. Otherwise, return a blank:

=IF(COUNT(--LEFT(A1)),--LEFT(A1,FIND(" ",A1)-1),"")

123 Main Street returns numeric 123

Main Street returns a blank

 

[HSL]

Thanks!

Assuming that the cell entry is:

123 Main Street

You can test the first character to see if it's a number. If it's number
extract the number to the first space character. Otherwise, return a blank:

=IF(COUNT(--LEFT(A1)),--LEFT(A1,FIND(" ",A1)-1),"")

123 Main Street returns numeric 123

Main Street returns a blank

 

[HSL]

Great!

How would I get the street name by itself now?

 

[Ron Rosenfeld]

ssuming that the cell entry is:

123 Main Street

You can test the first character to see if it's a number. If it's number
extract the number to the first space character. Otherwise, return a blank:

=IF(COUNT(--LEFT(A1)),--LEFT(A1,FIND(" ",A1)-1),"")

123 Main Street returns numeric 123

Main Street returns a blank

1st Avenue returns a #VALUE! error.
--ron

 

[T. Valko]

1st Avenue returns a #VALUE! error.
--ron

Yep. In that case we'll just have to return the street number as a text
value:

=IF(COUNT(--LEFT(A1)),LEFT(A1,FIND(" ",A1)-1),"")

 

[Ron Rosenfeld]

Yep. In that case we'll just have to return the street number as a text
value:

=IF(COUNT(--LEFT(A1)),LEFT(A1,FIND(" ",A1)-1),"")

I was not clear:

1st Avenue is the street without a number

473 1st Avenue might be a street address with a number.

I think you need to differentiate where the initial "word" is part of the
street name, or not.

Some examples where confusion might occur:

147 1st Avenue

2nd Avenue (No street address)

3A 4th Street

2 1/2 Middlesex Rd

4½ 92nd St.

--ron

 

[T. Valko]

I was not clear:

1st Avenue is the street without a number

473 1st Avenue might be a street address with a number.

I think you need to differentiate where the initial "word" is part of the
street name, or not.

Some examples where confusion might occur:

147 1st Avenue

2nd Avenue (No street address)

3A 4th Street

2 1/2 Middlesex Rd

4½ 92nd St.

--ron

This is why I rarely respond to text parsing questions regarding names and
addresses!

 

[Ron Rosenfeld]

This is why I rarely respond to text parsing questions regarding names and
addresses!

When I respond, it's usually to point out the inherent difficulties ). Some
of them are easy enough, but for something like this OP's request, one probably
needs to get into tables.
--ron