Trim Text From Left Of Second Hyphen

[binar]

Fellow Forum Members,
How can the code below be modified so that it Trims all text located to the left of HYPHEN that follows the RRRRR shown below?

=MID(A2,FIND("-",A2)+1,99)

Example Format:
K00005-RRRRR-10-444-5555-667

In other words, the result I'm seeking is:
10-444-5555-667 (the K00005-RRRRR- text is trimmed out via the formula)

I need the formula to be able to FIND that specific hyphen even if the quantity of characters that precede the second hyphen from left fluxuates.

Any help will be greatly appreciated. Thanks.

 

[MrKowz]

Try:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">K00005-RRRRR-10-444-5555-667</td><td style=";">10-444-5555-667</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=REPLACE(<font color="Blue">A1,1,SEARCH(<font color="Red">"$$",SUBSTITUTE(<font color="Green">A1,"-","$$",2</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Aladin Akyurek]

Fellow Forum Members,
How can the code below be modified so that it Trims all text located to the left of HYPHEN that follows the RRRRR shown below?

=MID(A2,FIND("-",A2)+1,99)

Example Format:
K00005-RRRRR-10-444-5555-667

In other words, the result I'm seeking is:
10-444-5555-667 (the K00005-RRRRR- text is trimmed out via the formula)

I need the formula to be able to FIND that specific hyphen even if the quantity of characters that precede the second hyphen from left fluxuates.

Any help will be greatly appreciated. Thanks.

Something like...

=REPLACE(A2,1,FIND("#",SUBSTITUTE(A2,"-","#",2)),"")

 

[cgcamal]

Hi binar,


Try with:

Excel Workbook
	A	B
1	K00005-RRRRR-10-444-5555-667	10-444-5555-667
...

Cell Formulas
Range	Formula
B1	=RIGHT(A1,LEN(A1)-SEARCH("-",A1,SEARCH("-",A1,1)+1))

Best regards

 

[T. Valko]

Fellow Forum Members,
How can the code below be modified so that it Trims all text located to the left of HYPHEN that follows the RRRRR shown below?

=MID(A2,FIND("-",A2)+1,99)

Example Format:
K00005-RRRRR-10-444-5555-667

In other words, the result I'm seeking is:
10-444-5555-667 (the K00005-RRRRR- text is trimmed out via the formula)

I need the formula to be able to FIND that specific hyphen even if the quantity of characters that precede the second hyphen from left fluxuates.

Any help will be greatly appreciated. Thanks.

Try this...

=MID(A2,FIND("^",SUBSTITUTE(A2,"-","^",2))+1,100)

 

[binar]

WOW!

I never realized there could be so many ways to accomplish the same thing. Thanks to all who posted. I tremendously appreciate the help. I was stuck between a rock and a hard place. This forum is totally awesome!

 

[T. Valko]

WOW!

I never realized there could be so many ways to accomplish the same thing. Thanks to all who posted. I tremendously appreciate the help. I was stuck between a rock and a hard place. This forum is totally awesome!

You're welcome. Thanks for the feedback!

 

[MrKowz]

WOW!

I never realized there could be so many ways to accomplish the same thing. Thanks to all who posted. I tremendously appreciate the help. I was stuck between a rock and a hard place. This forum is totally awesome!

Cheers. Thank you for the feedback!