Split by last deliminator

[MechEngSk1]

Hi All,

I have been searching everywhere, but nothing came up.

I have long list of data (asett IDs). All of it is in the following format.

AA-BB-CC-CC001

The number of dash occurences can vary based on the level the asett is located in.
I need to extract the parrent of the asett to adjacent column. the parrent for the above example would be:

AA-BB-CC

The parrent is the values before the last "-". There can be as many as 10 "-"s and as little as 1 in the asett ID.

I have been looking at the split and trim functions in excel but I have not been able to define split/trim by last "-".

Could you please assist me in sorting this out? Preferably a macro as I will have to do this number of times in the future. Other methods to trim/split are ok as well.

 

[tlowry]

Instructions:

Make a Backup(s) there is no going back!

Test on a small file until you know it works the way you want it to

In Excel, enter development mode (alt F11)
Insert a new module (Insert | Module)
Copy this code into the module

Use this as a User Defined Function

Function GetLastPart(sIN) As String
    If Trim(sIN) = "" Then Exit Function
    Dim arr: arr = Split(sIN, "-")
    GetLastPart = arr(UBound(arr))
End Function

Excel 2000

	A	B
AA-BB-CC-CC001	CC001
aa--bbcc---iii-0987
ddd	ddd

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]0987[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]-9090[/TD]
[TD="align: right"]9090[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #E0E0F0"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B1[/TH]
[TD="align: left"]=GetLastPart(A1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B2[/TH]
[TD="align: left"]=GetLastPart(A2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B3[/TH]
[TD="align: left"]=GetLastPart(A3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]B4[/TH]
[TD="align: left"]=GetLastPart(A4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Teethless mama]

Excel Workbook
	A	B
1	AA-BB-CC-CC001	AA-BB-CC
2	AA-BB-CC001	AA-BB
3	AA-CC001	AA
Sheet1

 

[Rick Rothstein]

Another formula for you to try...

=SUBSTITUTE(A1,"-"&TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",99)),99)),"")

 

[MechEngSk1]

Brilinat, thanks to all of you for such a fast reply.

It has worked wonders.

 

[MechEngSk1]

Rick,

The formula works great until you get the same characters in the parent section as in the removed section. It deletes them both. See below.

Thanks for your help regardless.
[TABLE="width: 429"]
<TBODY>[TR]
[TD]32-12-10-FA001A-EL</SPAN>[/TD]
[TD]32-12-10-FA001A</SPAN>[/TD]
[/TR]
[TR]
[TD]32-12-10-FA001A-FA</SPAN>[/TD]
[TD]32-12-10001A</SPAN>[/TD]
[/TR]
[TR]
[TD]32-12-10-FA001A-MT</SPAN>[/TD]
[TD]32-12-10-FA001A</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL span=2></COLGROUP>[/TABLE]

 

[Rick Rothstein]

Rick,

The formula works great until you get the same characters in the parent section as in the removed section. It deletes them both. See below.

Thanks for your help regardless.
[TABLE="width: 429"]
<TBODY>[TR]
[TD]32-12-10-FA001A-EL</SPAN>
[/TD]
[TD]32-12-10-FA001A</SPAN>
[/TD]
[/TR]
[TR]
[TD]32-12-10-FA001A-FA</SPAN>
[/TD]
[TD]32-12-10001A</SPAN>
[/TD]
[/TR]
[TR]
[TD]32-12-10-FA001A-MT</SPAN>
[/TD]
[TD]32-12-10-FA001A</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

Good catch! Okay, then there is always this alternative...

=LEFT(A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",99)),99)))-1)

(not sure if this version offers any benefit over Teethless mama's formula or not anymore -- one extra function call)