Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Extracting # numbers from address
I trying to extract the unit numbers from an address which have the unit
number all over and then clearing the extracted #unit from the original column. Any easier formula? e.g 123 Albert St #01-01 #02-112 St James St 12 Tomlinson Rd #01-01/02 George Bldg #10-01-03 Smith St -- KH |
#2
|
|||
|
|||
=FIND("#",address)
will return the location of the # sign, (or an error if it is not there). You could then use LEFT(), RIGHT() and MID() functions to split up the address around that point and looking for the next space. Jerry KH wrote: I trying to extract the unit numbers from an address which have the unit number all over and then clearing the extracted #unit from the original column. Any easier formula? e.g 123 Albert St #01-01 #02-112 St James St 12 Tomlinson Rd #01-01/02 George Bldg #10-01-03 Smith St |
#3
|
|||
|
|||
Hi KH,
Try this formula (in a new column, say in B1)! It assumes that the addresses start at A1. Change the 'A1's in the formula appropriately. Fill-in the formula in the rest of the new column. =IF(ISNUMBER(FIND(" ",TRIM(RIGHT(A1,LEN(A1)-FIND("#",A1))))),LEFT(TRIM(RIGHT(A1,LEN(A1)-FIND("#",A1))),FIND(" ",TRIM(RIGHT(A1,LEN(A1)-FIND("#",A1))))-1),TRIM(RIGHT(A1,LEN(A1)-FIND("#",A1)))) Regards, B.R. Ramachandran "KH" wrote: I trying to extract the unit numbers from an address which have the unit number all over and then clearing the extracted #unit from the original column. Any easier formula? e.g 123 Albert St #01-01 #02-112 St James St 12 Tomlinson Rd #01-01/02 George Bldg #10-01-03 Smith St -- KH |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting numbers from a cell | Excel Discussion (Misc queries) | |||
Checking Winning Numbers in the Lottery. | Excel Discussion (Misc queries) | |||
2 more questions about extracting numbers from text strings | Excel Discussion (Misc queries) | |||
extracting numbers from string | Excel Discussion (Misc queries) | |||
extracting numbers with no more than 8-digits using advanced filtering | Excel Worksheet Functions |