Solved

Excel formula help

Posted on 2011-09-08
22
333 Views
Last Modified: 2012-05-12
this formula, provided by EE guru Gowflow (thank you),  almost works
I have attached file using the formula
We are trying to get Assessor Parcel Numbers, as provided by assessor, reformatted to match that used in our database.

=CONCATENATE(MID(SUBSTITUTE(A2,"-",""),1,SEARCH(" ",SUBSTITUTE(A2,"-",""))),TEXT(MID(SUBSTITUTE(A2,"-",""),SEARCH(" ",SUBSTITUTE(A2,"-",""))+1,LEN(SUBSTITUTE(A2,"-",""))-SEARCH(" ",SUBSTITUTE(A2,"-",""))),"0000"))

test-results.xlsx test results screenshot
0
Comment
Question by:wkittils
  • 7
  • 6
  • 5
  • +1
22 Comments
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
ID: 36507502
For the five examples, you might try:
="0" & LEFT(A2,2) & MID(A2,4,1) & TEXT(SUBSTITUTE(MID(A2,6,2),"-",""),"00") & MID(A2,FIND("|",SUBSTITUTE(A2,"-","|",3))+1,1) & TEXT(--MID(A2,FIND("|",SUBSTITUTE(A2,"-","|",4))+1,9)," 0000")

It is worth noting that your original data has a mix of spaces and non-breaking spaces. The formulas converts them all to regular spaces.
0
 
LVL 80

Expert Comment

by:byundt
ID: 36507528
If the first field might have a single digit in column A, then try:
=TEXT(LEFT(A2,FIND("-",A2)-1),"000") & MID(A2,4,1) & TEXT(SUBSTITUTE(MID(A2,6,2),"-",""),"00") & MID(A2,FIND("|",SUBSTITUTE(A2,"-","|",3))+1,1) & TEXT(--MID(A2,FIND("|",SUBSTITUTE(A2,"-","|",4))+1,9)," 0000")

I am guessing you have the following requirements. Please correct any misunderstandings.
1.  Column A data always has four hyphens, thereby creating five fields
2.  First field is three digits, using leading 0 if necessary
3.  Second field is one digit
4.  Third field is two digits, using leading 0 if necessary
5.  Fourth field is a single character
6.  Fifth field is four digits, using leading 0 if necessary
7.  Result is 12 characters
0
 

Author Comment

by:wkittils
ID: 36507605
byundt...checking with others for reply, will not know until tommorrow
0
 

Author Comment

by:wkittils
ID: 36510157
byundt, I/we think your assumptions are correct and the formula works. It has been tested, not by me, on the small set. I/we now need to run it against the full set (9,000 property records) and see what happens. The irregular format is a challenge.  We have a tool (so that you do not have to excel expert) that does many things, including matching/merge tens of thousands of property records from/to huge spreadsheets, with APN the best way to match (can also do by address,but more problems).

Anyway, I need to use the formula and then the tool, and see what happens. I wont do this till tomorrow. I will close the ticket and assign the points. I will let you know what happens. If we need further help, I will start another ticket.  We will have other excel issues soon, as I am working in a project that relies heavily on excel, so I look forward to working with you again. Thank you for your help.
0
 

Author Comment

by:wkittils
ID: 36528038
I've requested that this question be closed as follows:

Accepted answer: 250 points for byundt's comment http:/Q_27298989.html#36507528
Assisted answer: 250 points for byundt's comment http:/Q_27298989.html#36507502
Assisted answer: 0 points for wkittils's comment http:/Q_27298989.html#36510157

for the following reason:

Byundt must have a very big brain.
0
 

Author Comment

by:wkittils
ID: 36510169
Look forward to working with byundt again
0
 
LVL 80

Expert Comment

by:byundt
ID: 36510475
wkittils,
If you need help with implementation or if the formula still doesn't do what you want--please continue to post in this thread. No need to open a new ticket. Doing so makes the question more valuable in Experts-Exchange database.

For future reference, this question wasn't particularly difficult (at least for one of the regulars in the Excel Zone), but it really wasn't clear what the the rules of the conversion were supposed to be. Had you posted them in your first question, I'm sure that you'd have gotten the question answered satisfactorily in your first thread.

Brad
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36528039
wkittils
Sorry but I had put several comments on the past question where I provided you with the formulas and asked you to put the link for your new question as already had the reply for you but you never answered. I will post here the file that give you the result in VBA for you to check. I am forced to object to the closure of this question but will leave you the entire choice to deal with the answer after you check my solution. Sorry but you need to check threads especially when one gave you a solution and still need to work on it.

Load the file you will be prompt for column number where you original Tax Id exist put the number not the letter (if Col B put 2) and then you will be prompt where you need the converted Taxid to be I created Col D where the button exist. pls put 4 you may choose to change this. Pls chk the results.

byundt
pls do not take this interjection as an offence as due you all the respect.
gowflow
excel-for-test-of-apn-formula.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36528059
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27295607.html

Here is the link to the question I ma refering to with all the threads and questions with no answer.
gowflow
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Expert Comment

by:South Mod
ID: 36539298
I've requested that this question be closed as follows:

Accepted answer: 0 points for wkittils's comment http:/Q_27298989.html#36510157
Assisted answer: 250 points for byundt's comment http:/Q_27298989.html#36507502
Assisted answer: 250 points for byundt's comment http:/Q_27298989.html#36507528

for the following reason:

Restarting closure per author's original intent.<br /><br />SouthMod<br />Community Support Moderator
0
 

Author Comment

by:wkittils
ID: 36529514
I do not understand what happened. I tried to close ticket and assign points to both goflow and byundt. As a novice, I figured I had gotten a lot help/mileage and should close the ticket and start a new one, with a more updated beginning point, but it seems per this last part of the thread I should not have. My apologies all around for my mis-handling
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36531085
If you are not satissfied with the closure then ask for request attention ! the exclamation at the beginning of the question in the bottom right corner it is a red Exclamation point. Press it and put your comments and moderator will cancel the autoclosure.
rgds/gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36534196
wkittils
What happened is the following:
1) When I posted my comment ID: 36528039 I clicked on OBJECT which halted the autoclosure of this question.
2) Moderators interveened and judged that I did not participated in the question therefore I do not have the right to stop the autoclosure of the question.
3) They restarted the autoclosure of this question BEFORE your comment in ID: 36529514.

If you are satissfied with the way this question is being closed as per SouthMod request at the end of this question then you need not to do anything and the question will be closed accordingly.

If you are not satissfied with the way this question will be closed then you need to interveen and stop the autoclosure of this question by either pressing on OBJECT or request attention and put the reason why you object.

Hope above clarifies both my action and help you make a decision.
gowflow
0
 
LVL 80

Expert Comment

by:byundt
ID: 36535245
gowflow,
With respect, this question is asking for a formula to return a 12 character string with a leading zero and one or two embedded spaces.  While a macro can be a great solution to a problem like this, the one in http:/Q_27298989.html#a36528039 omits both features.

Brad

0
 

Author Comment

by:wkittils
ID: 36539299
the points are supposed to be split between byundt and gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36540942
SouthMod You are interpreting wrong. I was not awarded 500 points on the earlier question but split between other and was awarded 167 points. The formulas which was the beginning of this question was the one I proposed and to which I gave a VBA solution now if it is full or not it is the asker to decide. I do not intent to make a fuss out of this you can check all my thread and see how much time I spend with askers and effort and you will understand what type of person I am. Sorry but your protray of my image is wrong in the comment you just posted. I am not after points at all I was waiting for the asker to put a link to the new question as was still some issue to be fix reason why he decided to put a new question. This was soly his decision.
Rgds/gowflow
0
 
LVL 80

Expert Comment

by:byundt
ID: 36542245
gowflow,
A fine point in VBA is that it requires each variable to be declared as something, unlike certain other languages which permit you to list a series of variables that are declared as something. Variables which appear in a Dim statement but which aren't specifically declared as something will be Variant variable type.
Dim a, b, c As Long        'Incorrect way: a and b are Variant; only c is Long
Dim a As Long, b As Long, c As Long           'Correct way: all three variables are Long

Open in new window


Although it didn't affect whether your macro ran as you intended in this question, VBA's variable declaration idiosyncracy might be an issue in some future macro.

Brad
0
 

Author Comment

by:wkittils
ID: 36542429
All I was trying to do was give credit for good effort to both.

Sorry
0
 
LVL 29

Expert Comment

by:gowflow
ID: 36542462
Tks byundt
Your entirely correct and appreciate your comment. In regards to this question It has taken a turn that I did not intent nor like the way it is developping I am sorry if this is putting your solution in question as surely you were smart enough to make the appropriate comments like (if things were clear right from the start Experts could have helped better).

I am withdrawing my interjection here and ask moderators to close this question fully to benefit of byundt as frankly did not appreciate at all last comments by SouthMod on my intervention which totally untrue and does not corespond to the reality.

To make things perfectly clear I explain the events of things so they are not mis-interpreted worngly.

1) I answer/participate in question
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27295607.html
raised by wkittils together with other Experts.
2) After proposing sevral versions of my answer together with replies  wkittils closed the question by attributing 167 point to me and split for other 2 Experts (which obviously I ignore the split).
3) I was aware that my solution was not complete and I was surprised by the closure of the question but the asker specified that still somehting is missing (a zero at 4th position) and he would re-post a new question. I fugured that the asker wanted to award point for whoever help him and want to go further in the question in a new question.
4) So I posted a comment asking the asker to put a link to the new question in there as I do not get emails when related questions are posted.
5) 2 days went by seeing nothing I posted an other comment telling the asker that I have his solution if he wishes so to put a link for the new question.
6) When the question was answered (turned green on my participated question) I was surprised nothing had happened so I started to look for all the Excel questions to find if any had been asked by
wkittils. And to my surpise I found that 4 days ago the question was asked and is beeing replied by Excel formulas.
7) Feeling that I have an other choice VBA solution then I interjected and objected to the autoclosure of this question ( Ihave been told -which I frankly did not know- that it was more appropriate to just put a comment and let the asker decide and not to OBJECT) it seems this OBJECT was mis-interpreted and caused all this high tone and wrong path of issues.

Anyway just to make things clear: I am not the type of individual to look for points I am stricly after helping people to get solutions and felt in this case it was already tough to decode what the asker wanted (as it was not clear actually byundt laided out for him and seems the asker himself didn't know he had to go back to the users to make sure the interpretation was correct) that I felt I had to provide the answer. Frankly I did not even look at byundt solution and felt I needed to give the asker more choice especially that when he answered: "byundt, I/we think your assumptions are correct"

Finally Again I ask moderator and wkittils to let go this question as it was originally intended and sorry for all this "Fuss" as it has been put.

gowflow
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now