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?

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

--
Biff
Microsoft Excel MVP


"HSL" wrote in message
...
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?

Thanks!

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"HSL" wrote in message
...
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?

Great!

How would I get the street name by itself now?

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"HSL" wrote in message
...
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?

On Tue, 8 Jul 2008 00:20:42 -0400, "T. Valko" wrote:

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

"Ron Rosenfeld" wrote in message
...
On Tue, 8 Jul 2008 00:20:42 -0400, "T. Valko"
wrote:

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

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),"")

--
Biff
Microsoft Excel MVP

On Tue, 8 Jul 2008 14:18:54 -0400, "T. Valko" wrote:

"Ron Rosenfeld" wrote in message
.. .
On Tue, 8 Jul 2008 00:20:42 -0400, "T. Valko"
wrote:

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

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

"Ron Rosenfeld" wrote in message
...
On Tue, 8 Jul 2008 14:18:54 -0400, "T. Valko"
wrote:

"Ron Rosenfeld" wrote in message
. ..
On Tue, 8 Jul 2008 00:20:42 -0400, "T. Valko"
wrote:

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

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

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

--
Biff
Microsoft Excel MVP