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
1	AA-BB-CC-CC001	CC001
2	aa--bbcc---iii-0987	0987
3	ddd	ddd
4	-9090	9090

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B1 =GetLastPart(A1) B2 =GetLastPart(A2) B3 =GetLastPart(A3) B4 =GetLastPart(A4) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[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.

32-12-10-FA001A-EL</SPAN>	32-12-10-FA001A</SPAN>
32-12-10-FA001A-FA</SPAN>	32-12-10001A</SPAN>
32-12-10-FA001A-MT</SPAN>	32-12-10-FA001A</SPAN>

<TBODY>
</TBODY><COLGROUP><COL span=2></COLGROUP>

 

[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.

32-12-10-FA001A-EL</SPAN>	32-12-10-FA001A</SPAN>
32-12-10-FA001A-FA</SPAN>	32-12-10001A</SPAN>
32-12-10-FA001A-MT</SPAN>	32-12-10-FA001A</SPAN>

<TBODY>
</TBODY>

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)