Excel formula help

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

byundt...checking with others for reply, will not know until tommorrow

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.

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.

Look forward to working with byundt again

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

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

https://www.experts-exchange.com/questions/27295607/Excel-Formula.html

Here is the link to the question I ma refering to with all the threads and questions with no answer.
gowflow

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

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

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

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

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

the points are supposed to be split between byundt and gowflow

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

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

Select allOpen 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

All I was trying to do was give credit for good effort to both.

Sorry

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
https://www.experts-exchange.com/questions/27295607/Excel-Formula.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