vba to shorten cells last digits based on contents

[solidENM]

Hello,
I am working on taking data from one sheet, and saving into a new format to push into computer system. Our job number format is W1234[year][-rev#].

i need to remove the year# (and -rev if it exists). This will be used to populate other cells which do not care about the full W#. I cannot always remove the last 2 digits, because if there was a rev, I would need to remove the last 4 digits from the cell. I also cannot remove all "17" or "18" since those could be in the middle of the W#.

either of these values ccould be in cell J3.
w123418
w123417-1

need vba to shorten J3 to: w1234

I was thinking: if -, remove last 4 digits, if not, remove last 2 digits. I am not sure how to do this though. I am thinking this would be easy but i am having trouble finding what terms to use while searching this forum.

Thanks

 

[Joe4]

If your prefix ("W1234") always five characters?
If so, just use the LEFT function, i.e.
=LEFT(A1,5)

 

[Peter_SSs]

If the prefix can vary in length, then try

=LEFT(A1,FIND("-",A1&"-")-3)

 

[solidENM]

Hello,
I didnt get an email notification of any replies, but came back to share my solution.

Joe4, no, the length can vary from W#### to W#######-#


I was able to get this done with a 2 step process
Step 1: The below code will remove the -# if it exists.
Sheets("Sheet2").Range("J4").Replace "-*", "", LookAt:=xlPart


Step 2 ' remove last 2 from j4 [yr#]


Dim r As Range
Application.ScreenUpdating = False
For Each r In Sheets("Assembly").Range("j4")
If Len(r) > 2 Then
r = Left(r, Len(r) - 2)
End If
Next r


Thanks for the efforts guys! I appreciate all the help and knowledge

 

[Joe4]

I didnt get an email notification of any replies

That is usually due to Spam filters you may have on your end.

You didn't mention that you wanted a VBA solution, but I am glad you figured out something that works for you.

 

[Peter_SSs]

You didn't mention that you wanted a VBA solution, ...

Actually, the OP did mention that quite clearly, though I missed it too.

need vba to shorten J3 to: w1234

I was able to get this done with a 2 step process

Good that you got a solution though it is a bit hard to really follow as your 2 codes reference two different worksheet names and only act on a single cell (J4) even though the second code says "For Each r In .."

If it is of any interest to you, a whole column can be done in a single step with code like the following. My code acts on the Active Sheet in column J from row 4 down. Any of that could be adjusted of course.

Sub RemoveYearAndRevision()
  With Range("J4", Range("J" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(#="""","""",left(#,find(""-"",#&""-"")-3))", "#", .Address))
  End With
End Sub

 

[Joe4]

Actually, the OP did mention that quite clearly, though I missed it too.

My apologies!
I cannot believe we both missed that!!!