I did some shorthand here. I just hard coded the example you gave int
what I wrote. I figured I’d let you figure out how to pass in th
various cells’ values. Also I didn’t include any way for it to loo
through each of the cells you have, my recommendation, given how thi
works is to have it take the values from one sheet and send it’
results to a new sheet entirely, thus avoiding the need for it to kee
inserting rows to accommodate each SN. This code, as it is, will pu
the model number into the first column once for each serial number an
each serial number will go into the second column followed by the yea
in the third column and the letter that indicates the month in th
fourth. I figured you could convert the months… Let me know if yo
need any further assistance or explanation. I’ll be happy to do so
though I personally learn more for having to sort out all that stuf
myself… - Pikus

infoStr = "00B1233455 S/N 04A34565, 04B234565, 01F32456, 9G12345"
infoStrLen = Len(infoStr)
snCount = 0
For x = 1 To infoStrLen
If Mid(infoStr, x, 1) = " " Then
snCount = snCount + 1
End If
Next x
snCount = snCount - 1
ReDim snArray(snCount) As String
For x = snCount - 1 To 0 Step -1
snStart = InStrRev(infoStr, " ")
snStart = snStart + 1
snArray(x) = Mid(infoStr, snStart)
infoStr = Left(infoStr, snStart - 3)
Next x
snStart = InStrRev(infoStr, " ")
infoStr = Left(infoStr, snStart - 1)
For x = 1 To snCount
Cells(x, 1).Value = infoStr
Cells(x, 2).Value = snArray(x - 1)
If IsNumeric(Mid(snArray(x - 1), 3, 1)) Then
Cells(x, 3).Value = 1990 + Left(snArray(x - 1), 1)
Cells(x, 4).Value = Mid(snArray(x - 1), 2, 1)
Else
Cells(x, 3).Value = 2000 + Left(snArray(x - 1), 2)
Cells(x, 4).Value = Mid(snArray(x - 1), 3, 1)
End I

--
Message posted from http://www.ExcelForum.com