Extract serial number from a string and insert info

[James Tinker]

In my problem workbook, there are a few things I have to work with but cannot change.

The user clicks a button labeled "Enter Job Number" which starts the ball rolling and populates a number of cells in hidden columns A thru E.
Once that's done, the user then comes to a button that reads "Click to enter S/N". This launches the following macro...

Sub getserialnumbers()
Dim serialnumbers As String
serialnumbers = "'(" & InputBox("Enter serial numbers: " & serialnumbers) & ")"
Range("iSN").Value = serialnumbers
End Sub

The result winds up in C17 (named "iSN") and is used in a formula found in A17 (named "SN"). That formula produces a result dependent upon the contents of C12 (named "CUSTID").

Here's the formula...
=IF(iSN="","",IF(OR(LEFT(CUSTID,2)="KV",NOT(ISERROR(FIND("CAM",SHIPTO))),NOT(ISERROR(FIND("VALCO",SHIPTO)))),cam_sn,IF(CUSTID="HALLI",halli_sn,JOBNUMBER&" - "&iSN)))

So...the user clicks the button, enters the serial numbers (let's say "1-5") and then the result winds up in a merged cell, J31-O31 that contains the following formula...
=IF(SN="",SNTEXT,SN)

Okay, so we have a customer named XYZ with several locations. Their CUSTID changes depending on location but the first three characters are always XYZ. (i.e. "XYZ", "XYXG", "XYZEH", etc.) Let's say the job number is 112211 and there are five pcs numbered 1 thru 5. Following the process above, J31 would show "112211 - (1-5)".


SO HERE'S MY QUESTION...
Customer XYZ requires their serial numbers to be written a specific way. They want the line to read "112211-1 to 112211-5". No parenthesis, no spaces within each serial number, just the first FULL serial number and the last with "[space]to[space]" in between.

Please help.

Thanks for your time.

James Tinker

 

[James Tinker]

but the first three characters are always XYZ. (i.e. "XYZ", "XYXG", "XYZEH", etc.)

I apologize, that should read "(i.e. "XYZ", "XYZG", "XYZEH", etc.)"