Sub ConversionSpéciale()
ConvSpé Range("C4"), 0, 2 'première cellule de données, décalage de ligne de sortie, décalage de colonne de sortie
'Si l=c=0, les données traitées remplacent les données originales.
End Sub
Sub ConvSpé(r As Range, Optional l&, Optional c&)
With Range(r.Cells, r.Cells.End(xlDown))
On Error GoTo E
.TextToColumns Destination:=.Offset(l, c), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True '*
.Offset(l, c + 1).Cut Destination:=.Offset(l, c + 3)
On Error GoTo 0
Application.DisplayAlerts = False
.Offset(l, c).TextToColumns Destination:=.Offset(l, c), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, OtherChar _
:=":", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
TrailingMinusNumbers:=True
Application.DisplayAlerts = True
.Offset(l, c + 5).FormulaR1C1 = "=IF((RC[-2]<>"""")*(RC[-3]<>""""),RC[-5],"""")" '*
.Offset(l, c + 6).FormulaR1C1 = "=IF(RC[-1]<>"""",RC[-5],IF(RC[-5]<>"""",RC[-6],""""))"
.Offset(l, c + 7).FormulaR1C1 = "=CHOOSE(1+COUNTBLANK(RC[-2]:RC[-1]),RC[-5],RC[-6],RC[-7])&"":""&RC[-4]"
.Offset(l, c + 5).Resize(, 3).Value = .Offset(l, c + 5).Resize(, 3).Value
.Offset(l, c).Resize(, 5).Delete Shift:=xlToLeft
.Offset(l, c).Resize(, 3).EntireColumn.AutoFit
E: End With
End Sub