Removing part of the date from a cell

[Lindsey_Hood]

Hello

I have a large spreadsheet that I need to manipulate and am hoping someone can help me.

An example of the data is below:

<TABLE style="WIDTH: 224pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=299><COLGROUP><COL style="WIDTH: 224pt; mso-width-source: userset; mso-width-alt: 10934" width=299><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 224pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=299>030-2409-001-P00-ENC/N</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 224pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=299>030-2409-003-ITT/N</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 224pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=299>030-8587-006-ITT/N</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 224pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=299>030-8592-000-ITT/N</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 224pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 height=20 width=299>030-9542-001-ITT/N</TD></TR></TBODY></TABLE>

Basically, I want to remove everything after the last - (i.e. -ENC/N on the first line).

This is something that I have done before with an excel formula but I have lost the formula.

Thanks for your help in advance.

Lindsey

 

[mikerickson]

Try
=TRIM(LEFT(SUBSTITUTE(A1,"-",REPT(" ",255),3),255))

Or, if the numeric part is always the same length.

=LEFT(A1, 12)

 

[VoG]

Try

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

 

[shg]

If the result for the first is to be 030-2409-001-P00, then

=LEFT(A1, FIND("^", SUBSTITUTE(A1, "-", "^", LEN(A1) - LEN(SUBSTITUTE(A1,"-","")))) - 1)