Hi All,
My data is as below
============
AB-FI-BCD-12345
DG-RK-NNN-987095
RR-ABCDEFGHIJK-9873
TPQ-STUBCKL-12
PPP-ZZZ-1
my requirement is to count the number of characters after last hyphen. If the count is >=4 then add hyphen after 3 characters and display the whole string
THE END DATA SHOULD BE AS BELOW
======================
AB-FI-BCD-123-45
DG-RK-NNN-987-095
RR-ABCDEFGHIJK-987-3
TPQ-STUBCKL-12
PPP-ZZZ-1
Please help me with a formula.
Try this formula
=IF(FIND("^",SUBSTITUTE(A1,"-","^",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))+4>LEN(A1),A1,REPLACE(A1,FIND("^",SUBSTITUTE(A1,"-","^",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))+4,0,"-"))
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Nice 1 CB, much better than the beast I came up with...
=IF(LEN(MID(A1,FIND("zz",SUBSTITUTE(A1,"-","zz",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))),1)+1,99))<4,A1,SUBSTITUTE(A1,MID(A1,FIND("zz",SUBSTITUTE(A1,"-","zz",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))),1)+1,99),LEFT(MID(A1,FIND("zz",SUBSTITUTE(A1,"-","zz",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))),1)+1,99),3))&"-"&RIGHT(MID(A1,FIND("zz",SUBSTITUTE(A1,"-","zz",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))),1)+1,99),LEN(MID(A1,FIND("zz",SUBSTITUTE(A1,"-","zz",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))),1)+1,99))-3))
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Impressive, Ford
Not as impressive as what you came up with
Finesse wins out over brute force every time
your formula worked like a charm.
But, can u explain it. I couldn't understand. why you are using ^ this symbol?
I took a similar approach to ChemistB:
Is that something you can work with?
Please Login or Register to view this content.
Thank you for your replies. Thank u so much for helping me out. But chemist B can u explain the formula and use of ^
Try this formula
=IF(ISERR(RIGHT(A1,5)+0),A1&"",REPLACE(A1,LOOKUP(2^15,FIND("-",A1,ROW(INDIRECT("1:"&LEN(A1)))))+4,0,"-"))
Audere est facere
the ^ is being used to replace the last - (found using the substitute() function). then that is being used to find its position - count that +4 and add a -
Can this formula be incorporated to see also if the string starts with "AB" if so modify to "ABC" and do the above.
raw data
======
AB-XY-12345
DD-PQ-1234
AB-ZZ-123466
FINAL DATA:
========
ABC-XY-123-45
DD-PQ-123-4
ABC-ZZ-123-466
I don't know how the merging of formulas to evaluate multiple conditions can be done?
As always, DDL wins for shortest formula.
Okay, budd
The IF part is just checking to make sure that if there is 3 or less characters after the final hyphen to keep it the same, so let's focus on
REPLACE(A1,FIND("^",SUBSTITUTE(A1,"-","^",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))+4,0,"-"))
We want to find the position of the final hyphen but there may be more than one hyphen. With the substitute, I can change that final hyphen into a symbol (or symbols as FDibbins did with "zz") that is unlikely to appear elsewhere in your text. I chose "^"
The substitute function allows us to pick which occurrence of the hyphen we want to change. We can't say "last" so we need to know how many hyphens there are. LEN(A1) - LEN(SUBSTITUTE(A1,"-","")) does that.
So now
SUBSTITUTE(A1,"-","^",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))) will replace the third hyphen (in this case) with a ^
So FIND("^",SUBSTITUTE(A1,"-","^",LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))) gives us the location of that ^
Doing some substitution to make it clearer, we're left with
REPLACE(A1, Position of last hyphen + 4, 0 , "-")
or, in English, At the position of the last hyphen + 4, replace no characters with a hyphen.
Is that helpful? I suggest you select a cell with the formula in it and go to the Formulas tab and "Evaluate Formula" Then you can see it step by step.
You could modify my suggested formula. This will change AB at the start to ABC and add the extra "-" if requiredOriginally Posted by buddigars
=REPLACE(IF(ISERR(RIGHT(A1,5)+0),A1,REPLACE(A1,LOOKUP(2^15,FIND("-",A1,ROW(INDIRECT("1:"&LEN(A1)))))+4,0,"-")),3,0,IF(LEFT(A1,2)="AB","C",""))
Last edited by daddylonglegs; 09-19-2014 at 11:43 AM.
I note that this question is also posted here
Please note that cross-posting without a link is against our forum rules
Try it with 8 functions:
=TRIM(SUBSTITUTE(REPLACE(A2,FIND("ß",SUBSTITUTE(A2,"-","ß",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))+4,0,"-")&" ","- ",""))
Last edited by István Hirsch; 09-19-2014 at 03:41 PM.
With AB to ABC:
=TRIM(SUBSTITUTE(" "&SUBSTITUTE(REPLACE(A2,FIND("ß",SUBSTITUTE(A2,"-","ß",LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))))+4,0,"-")&" ","- ","")," AB","ABC"))
thank you all for helping me out
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks