Temo que não consiga pensar em um método simples de fórmula, no entanto, aqui está um método VBA usando RegEx, caso seja de alguma utilidade para você. O padrão RegEx assume que os códigos serão sempre os mesmos, 4 letters - 1 letter - 4 digits , é claro que você pode corrigir conforme necessário. Se a suposição de letras e dígitos estiver incorreta, mas o formato for sempre 4-1-4, você poderá usar .{4}\-.\-.{4} :
SubGetCodes()DimstrPattern:strPattern="\w{4}\-\w\-\d{4}" 'Pattern to match
Dim colNumber: colNumber = 1 'Column number containing strings (In this case, 1, for column A)
Dim rowCount: rowCount = 1 'Row number to start from
Range("B1").Select 'Cell to start new column from
'Create a new RegEx engine instance
Dim rgx: Set rgx = CreateObject("vbscript.regexp")
'Set out RegEx instance to allow Global (More than 1 result per text), MultiLine (Incase there are any carriage returns in the cell), IgnoreCase (Allow both upper and lowercase, which isn't needed with \w but included to be sure) and Pattern, the patter defined above.
With rgx
.Global = True
.MultiLine = True
.IgnoreCase = True
.Pattern = strPattern
End With
'Begin a loop that ends once we hit an empty cell
Do
'Get all our RegEx matches and store them in rgxMatches
Dim rgxMatches: Set rgxMatches = rgx.Execute(Cells(rowCount, colNumber).Value)
Dim rgxMatch
'Loop through our matches
For Each rgxMatch In rgxMatches
'Write the match into the active cell
ActiveCell.Value = rgxMatch.Value
'Go down one row, ready to write the next cell if there is one
ActiveCell.Offset(1, 0).Select
Next
'Increment our row count so the next loop uses the next row
rowCount = rowCount + 1
Loop Until IsEmpty(Cells(rowCount, colNumber))
'Clean up after
Set rgx = Nothing
Set rgxMatches = Nothing
End Sub
