Removing house numbers from addresses

I have a spreadsheet containing 18,000 addresses. In some cases the
first line of the address (in Column A) contains a house number e.g.

25 High Street
124 Cherry Ave
7 King St

In other cases, the address contains no house number e.g.

Highgrove
Summerseat


Is there any way of writing the address line 1 to a new column and
deleting the house numbers from any addresses that have one? So in the
examples above I would like to end up with the following in the new
column:

High Street
Cherry Ave
King St
Highgrove
Summerset


--
dalymjl

If it is always a space between the number and the rest you could use

=IF(ISERROR(--(LEFT(TRIM(A1)))),A1,TRIM(SUBSTITUTE(A1,LEFT(A1,FIND("
",A1)),"")))

--
Regards,

Peo Sjoblom


"dalymjl" <[email protected]> wrote in message
news:[email protected]...
>
> I have a spreadsheet containing 18,000 addresses. In some cases the
> first line of the address (in Column A) contains a house number e.g.
>
> 25 High Street
> 124 Cherry Ave
> 7 King St
>
> In other cases, the address contains no house number e.g.
>
> Highgrove
> Summerseat
>
>
> Is there any way of writing the address line 1 to a new column and
> deleting the house numbers from any addresses that have one? So in the
> examples above I would like to end up with the following in the new
> column:
>
> High Street
> Cherry Ave
> King St
> Highgrove
> Summerset
>
>
> --
> dalymjl

Try this:

=IF(ISERR(--LEFT(A1,FIND(" ",A1))),A1,MID(A1,FIND(" ",A1)+1,100))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"dalymjl" <[email protected]> wrote in message
news:[email protected]...

I have a spreadsheet containing 18,000 addresses. In some cases the
first line of the address (in Column A) contains a house number e.g.

25 High Street
124 Cherry Ave
7 King St

In other cases, the address contains no house number e.g.

Highgrove
Summerseat


Is there any way of writing the address line 1 to a new column and
deleting the house numbers from any addresses that have one? So in the
examples above I would like to end up with the following in the new
column:

High Street
Cherry Ave
King St
Highgrove
Summerset


--
dalymjl

nice one, if there is a street number one might still want to sort
on the street number as well for those that do have them.
To return an empty string, or beginning number.
=IF(ISERR(--LEFT(A1,FIND(" ",A1))),"",LEFT(A1,FIND(" ",A1)-1))

both Peo's and RagDyer's solutions do return a zero on a blank cell,
so I modified RD solution for the optional street number.

"RagDyeR" <[email protected]> wrote ...
> =IF(ISERR(--LEFT(A1,FIND(" ",A1))),A1,MID(A1,FIND(" ",A1)+1,100))

Ran into something similar but had an issue because some of my addresses didn't have a street number or might have a number in the street like Highway 80 or US 101. This solution resolved those issues:

Separates the street number into another column
=LEFT(A2,FIND(" ",A2,1))

Removes the street number that was separated out into column B leaving the street name
=SUBSTITUTE([A2,[B2],"")