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 |
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,FI ND(" ",A1)),""))) -- Regards, Peo Sjoblom "dalymjl" wrote in message ... 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" wrote ... =IF(ISERR(--LEFT(A1,FIND(" ",A1))),A1,MID(A1,FIND(" ",A1)+1,100)) |
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" wrote in message ... 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 |
All times are GMT +1. The time now is 05:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com