How do I correct my formula to place the leading zero at the end of my naming (cross post)
Hi all. Seems like my post on another forum made more than 30 hours ago has been buried under the bush, just want bump it up here.
I can't link the post as I;m a new user. So just breaking it up.
www . mrexcel . com/board/threads/what-am-i-missing-in-my-formula-for-adding-leading-zeroes-to-my-list.1156323
Thanks,
Sam
Last edited by Sampoline; 12-19-2020 at 06:43 AM. Reason: On request
OK, but you will need to tell us what the problem is that you wish to solve. This is a different forum: nobody is going to go to another forum to find out what the issue is. If you are just expecting people to go over there and help you there, then I am afraid that's not allowed, so please give ddetails of the issue here. Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Administrative Note:
We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.
Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).
Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.
(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
Hi sorry I'm new. I was told when cross posting not to create the question anew on a different forum, so that's why I just added the link. Seems like I misunderstood. Anyway I'll begin my question from the start as per your request.
I'm having an issue with a formula I have for adding a leading zero to the end of all my cells. I have attached a workbook. Please review it.
Column B has the formula I have so far. Which is:
Formula:
Please Login or Register to view this content.
But this isn't correct entirely. In the workbook, the mistakes in Column B are highlighted in red. Column C has the correct naming. But they are just values, how can fix my formula to reflect the values in Column C. (I don't want the 3rd column, I want to fix Column B).
I'm a bit lost.
Thanks,
Sam
See post #3 - update your title to something explicit of your problem, please, and then I'll have a look. Thanks.
Sorry. The title has been updated. Is this okay now?
Yes - I am looking at it now.
This works on your small data sample:
=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(A2,"/","-"),"-"," ",3),10))&"-"&IFERROR(TEXT(VALUE(TRIM(RIGHT(SUBSTITUTE(RIGHT(A2,4),"/",REPT(" ",10)),5))),"00"),RIGHT("0"&TRIM(RIGHT(SUBSTITUTE(RIGHT(A2,4),"/",REPT(" ",10)),5)),3))
Just spotted an issue with it - sorry. Will have another look.
No problem, thanks for looking into it.
A bit of brain freeze - I think I'm going to look at a different approach. Look in again later!
Yeah it's got me in a bit of a head spin the past couple of days!
OK, so this is a PowerQuery solution (you can download the add-in from here:https://www.microsoft.com/en-gb/down....aspx?id=39379), which is an integral part of Excel 2016 and above.
M Code:
Excel 2016 (Windows) 32 bit
Please Login or Register to view this content.
AliGW A-VG-Z1-01 A-VG-Z1-02b A-VG-Z1-03.1 A-VG-Z1a-04 A-VG-Z1a-05 A-VG-Z1a-06 A-VG-Z1a-07 A-VG-Z1-08 A-VG-Z1-09 A-VG-Z1-10a A-VG-Z1-11
Sheet: Sheet1
Last edited by AliGW; 12-19-2020 at 08:35 AM.
I just updated post #13.
Hi Ali,
Thanks for so much for this. You've saved me so much headache over the last couple of days honestly. So this is the first time I have ever dealt with Power Queries. Will this work fine with Excel 2013 too? I'm at home at the moment so I'm using 365. But at work they use 2013. Will need to adjust the query to that particular spreadsheet. Just want to double-check that it will be fine?
Thanks,
Sam
This I cannot answer, as I don't have Excel 2013. As long as they will allow the add-in, it should work - I don't think that any of the steps I've used won't work in an older version - but you'd have to try it.
Maybe someone with Excel 2013 who sees this could try it for us and let us know. If not, you'll have to wait and see. Sorry!
Hi Ali,
Yep I will have to check back on Monday when I get into the office to make sure it works. But I can confirm to you right now that Power Query is 100% available as an add-in on Excel 2013. Just never used it for any of my work.
Right - I know that, but sometimes companies won't install add-ins, and that's what I was querying. If you know they'll install it for you at work, then we're halfway there.
Awesome thanks for asking about that. I will confirm all this soon!
In the meantime, I've been looking at the M code to understand what all of it means. I am having a bit of difficulty understanding what's going in there as newbie. Now if I have to bring this power query over to my custom spreadsheet at work; seems a little daunting for me. As I have different column names. I'm assuming if column names are different, I'm changing the text in red to the appropriate name. But I'm not able to get my head around for example:
What does Current Name.1 and Current Name.2.2 represent exactly? What is different between the two Current Name(s)?Please Login or Register to view this content.
In understanding that, say there comes a point where I have a name that is like "A/VG/Z1/7.1.1" how would I have to adjust the m code to account for the extra ".1" at the end? If that makes sense.
Thanks for your patience.
OK - I think the thing to do is to step through the steps rather than looking at the code itself: everything I did can be done using UI clicks. You can do this by clicking sequentially from top to bottom on the stages to the right and looking at what happens in the UI.
Queries are easy to adapt once you know what they are doing.
I can write a step-by-step if you wish, but it would take me quite a while!
That's called a spanner in the works! You really should have thought about this scenario when you posted the sample data. Back to the drawing board!In understanding that, say there comes a point where I have a name that is like "A/VG/Z1/7.1.1" how would I have to adjust the m code to account for the extra ".1" at the end? If that makes sense.
Last edited by AliGW; 12-19-2020 at 11:22 AM.
OK - here's a new version that should allow for the different levels at the end of the code.
M Code:
Please Login or Register to view this content.
Excel 2016 (Windows) 32 bit
Merged A-VG-Z1-01 A-VG-Z1-02b A-VG-Z1-03.1 A-VG-Z1a-04 A-VG-Z1a-05 A-VG-Z1a-06 A-VG-Z1a-07 A-VG-Z1-08 A-VG-Z1-09 A-VG-Z1-10a A-VG-Z1-11 A-VG-Z1-07.1.1
Sheet: Sheet1
Thanks for your help Ali. I'm starting to understand this. Will report back tomorrow when I get it done on my work spreadsheet!
Great! There will probably be workarounds if any of the steps fail in the older add-in, so just shout.
Hi Ali,
I've just tried opening up your spreadsheet on Excel 2013 and ran into the following error at 'Step 9'.
Attachment 709707
Previous steps are showing up fine it seems..
The attachment is invalid, so I can’t see it. Tell me what the error is.
The error is as follows:
Expression.Error: The name 'Splitter.SplitTextByCharacterTransition' wasn't recognized. Make sure it's spelled correctly.
The Applied Step was 'Split Column by Character Transition':
= Table.SplitColumn(#"Added Prefix", "Current Name.2.1", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Current Name.2.1.1", "Current Name.2.1.2"})
OK - I thought that might be the one to trip it up. Leave it with me and I'll try to create a workaround for that step. Will report back later.
Thankyou so much!
Try this version.
A formula solution:
Please Login or Register to view this content.
Quang PT
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
Having spent a significant amount of time helping you with this, I am keen to know whether my latest version has worked for you - some feedback would be appreciated.
Hi Ali
Sorry for the delayed response, I was feeling unwell the last couple of days so was away from the computer.
I've just had a look at the spreadsheet and this finally works! The table is loading and refreshing with no errors! Thank you so much for being patient and working hard. Saved me from further headache here.
Glad to meet you.
Thanks,
Sam
That’s great. Please see post #31 fir instructions how to sign off the thread.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks