Hi Excel Forum world,
I am having trouble cracking this one! I have a situation where a client wants me to extract all prefixes to an address column"
What I have so far is:
E2 = "Level 5, Test Street"
C2 = "Level" (extracted from E2)
D2 = Needs to display "Level 5," using C2 as a reference because this value changes down column C and could say Unit, Apartment etc.
Can anyone suggest a way I can do this with either formula or VBA?
Many thanks,
Brendan
Assuming that the level number in E2 will always be followed by a comma, the following formulas seem to work:
In C2: ="Level "&MID(E2,FIND(",",E2)-2,2)
In D2: =C2
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Hi JeteMc,
Thanks for the reply!
This almost works except for a few issues:
1. The number may not follow a comma and there may be other commas in the address so finding a comma will not work.
2. the value in column C is found using an INDEX, MATCH so it will not always be Level and it may be blank.
Here are a few examples:
11 Harbour Street, Darling Quarter, Level 1, South Building
- Needs to return Level 1
19 Herbert St, Unit 10
- Needs to return Unit 10
Also the number will not always be 2 digits.
I suggest uploading a sample spreadsheet with examples of types of input that could possibly be found in column E along with expected outputs in columns C and D.
To attach a sample spreadsheet click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.
Just a demo
Please Login or Register to view this content.
Test Doc attached
Paste the following formula into D2, press the enter key and double click the fill handle to copy down:Formula:
Please Login or Register to view this content.
Let us know if you have any questions.
Thanks mate this almost worked perfectly, just a few issues.
There are some addresses that do not adhere to a standard convention that are not being picked up correctly in the return.
The issues are in the attached document but this is the list of reasons I can see it not working:
- Unit number before slash
- No separator at all
- Unit number before dash
- Unit number at end of address
- Comma after full street address
- Number before slash but word apartment elsewhere in address
- Ground Level
I know this a lot of variables but I am hoping that there is way that you can have the formula pick up only:
1. The unit type
2. The numbers After the unit type and Before the next space.
Thanks for your help.
Hi JeteMc,
Do you think that my below request can be done?
Thanks again for your help so far.
I will show you what I have come up with so far, it doesn't find all of them, but does find some.
The formula employed is:Formula:Please Login or Register to view this content.
Perhaps someone on the VBA side will take another look at the file attached to post #9.
Thanks very much this is great and addresses most of the .... addresses!
I attacked this from another direction, five helper columns (which may be hidden for aesthetic purposes). The first helper (AB) sets the starting point for the number to be extracted using the array entered formula*:Formula:Please Login or Register to view this content.
The second through fourth helpers (AC:AF) are similar using variations of the formula:Formula:Please Login or Register to view this content.
Finally the formula that populates column D is:Formula:Please Login or Register to view this content.
*Array entered formulas are activated by simultaneously pressing the Ctrl, Shift and Enter keys while the cell is still in edit mode.
More helper columns could be added if the largest number to be extracted has more than 4 digits.
Note: Attempted to address the issue in row 34 (/ and apartment) by rearranging the order of the Lists sheet column A.
Let us know if you have any questions.
Once more.
Deleted the formula from columns AC:AF of the post above and instead used the following array entered formula* in AC:Formula:Please Login or Register to view this content.
Where Lists!C$2:C$4 references the list of separators: Space, Period and Slash
The formula that populates column D is:Formula:Please Login or Register to view this content.
Seems to do better getting addresses such as the ones in rows 15, 31 and 43.
Let us know if you have any questions.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks