Solved

Excel formula help

Posted on 2011-09-08
22
339 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 5
  • +1
22 Comments
 
LVL 81

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 81

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 81

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 30

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 30

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
 

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 30

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 30

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 81

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 30

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 81

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 30

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

705 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