Boostez vos compétences Excel avec notre communauté !
Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !
=GAUCHE(A1;EQUIV(VRAI;CODE(STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)));1))<97)-1)
&" "&STXT(A1;EQUIV(VRAI;CODE(STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)));1))<97);99)
Sub test_feuillev2()
Dim r As Range, a$
ActiveSheet.Cells.Clear
[A1] = "hubertDewagne"
[A2] = "HubertDewagne"
[A3] = "Eric-HubertDewagne"
[A4] = "eric-hubertDewagne"
With CreateObject("vbscript.regexp")
.Pattern = "([a-z])([A-Z])"
.Global = True
For Each r In [A1].CurrentRegion
If .Execute(r).Count Then
a = Application.Proper(Split(.Replace(r, "$1 $2"))(0))
r.Offset(, 1) = _
a & " " & Split(.Replace(r, "$1 $2"))(1)
End If
Next r
End With
End Sub
[COLOR="DarkSlateGray"][B]Sub insertion_espace_1()
Dim oCel As Range, i&
With Selection [COLOR="Olive"]'ou tout autre plage qu'on voudra...[/COLOR]
For Each oCel In .Cells
If VarType(oCel) = vbString Then
For i = Len(oCel.Value) To 2 Step -1
If Mid$(oCel.Value, i, 1) = UCase(Mid$(oCel.Value, i, 1)) Then
oCel.Value = WorksheetFunction.Trim(Left$(oCel.Value, i - 1) & Space(1) & Right$(oCel.Value, Len(oCel.Value) + 1 - i))
End If
Next i
End If
Next oCel
End With
End Sub[/B][/COLOR][COLOR="DarkSlateGray"][B]Sub insertion_espace_2()
Dim oCel As Range, tmp$, i&
With Selection [COLOR="Olive"]'ou tout autre plage qu'on voudra...[/COLOR]
For Each oCel In .Cells
If VarType(oCel) = vbString Then
tmp = oCel.Value
For i = Len(tmp) To 2 Step -1
If Mid$(tmp, i, 1) = UCase(Mid$(tmp, i, 1)) And Mid$(tmp, i, 1) <> "-" Then
If (Mid$(tmp, i + 1, 1) <> UCase(Mid$(tmp, i + 1, 1)) Or Mid$(tmp, i - 1, 1) <> UCase(Mid$(tmp, i - 1, 1))) And Mid$(tmp, i - 1, 1) <> "-" Then
tmp = Left$(tmp, i - 1) & Space(1) & Right$(tmp, Len(tmp) + 1 - i)
End If
End If
Next i
oCel.Value = WorksheetFunction.Trim(tmp)
End If
Next oCel
End With
End Sub[/B][/COLOR]
Et bien, je proposerais une petite fonction
Function AjouteBlanc(ATester As String) As String
Dim Match, Matches, I As Integer
Application.Volatile
With CreateObject("vbscript.regexp")
.Global = True
.Pattern = "(^[a-z]+(?=[A-Z]))|(^[a-z]+\-[a-z]+(?=[A-Z]))|" _
& "([A-Z][a-z]+\-[A-Z][a-z]+)|([A-Z][a-z]+|[A-Z]+$)|([A-Z]+(?=[A-Z]))"
Set Matches = .Execute(ATester)
If Matches.Count = 0 Then
AjouteBlanc = ""
Else
For Each Match In Matches
AjouteBlanc = AjouteBlanc & " " & Application.Proper(Match)
Next
AjouteBlanc = Right(AjouteBlanc, Len(AjouteBlanc) - 1)
End If
End With
End FunctionBien vu !
[COLOR="DarkSlateGray"][B] oCel.Value = WorksheetFunction.Trim(tmp)
[/B][/COLOR][COLOR="DarkSlateGray"][B] With WorksheetFunction: oCel.Value = .Proper(.Trim(tmp)): End With[/B][/COLOR]Merci. j'avais testé la votre et avait vu aussi cette petite différence, sachant très bien que ce n'était qu'une différence de Proper 😉.
