Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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)) |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count and Sum Total occurrances of two specific numbers | Excel Worksheet Functions | |||
Using Macro to Sort Names, Numbers & addresses | Excel Worksheet Functions | |||
Converting Numbers to Text properly | Excel Discussion (Misc queries) | |||
removing firsts two numbers from a list of #s | Excel Worksheet Functions | |||
How to Replace numbers and text with numbers only? | Excel Worksheet Functions |