If the hyphen character (-) does not exist, it is inserted in a specific place.

[harzer]

Hello everyone,
My data is in columns "A", "B", "C", "D", "E","F", "G", "H", "I", "J", "K" .
In this case, we will work only on the first three columns: "A", "B" and "C".
To show you that all the cells of these three columns have the same format, we will read them from right to left.
Take for example the content of cell "A2": AETYTT-093/2012 M
1. The cell in our example ends with "M", in other cells, we find an "F".
2. We always continue to read from right to left and we find a space.
3. Next to the space, there is a 4-digit number.
4. After the four digits, there is the slash (/) character.
5. After the slash character (/), there is a three-digit number.
Normally, after these three digits, there should be a hyphen (-). --> this is the case for cell "A2", so nothing is changed in this cell.
We will now see how to proceed to correct our cells:

a) If after the three-digit number, there is a dash, this means that nothing has changed and that the content of the cell is correct.
b) If after the three-digit number, there is a character other than a hyphen, a hyphen is inserted after the three digits.
c) If after the three-digit number, there is a space, or two, or three spaces, we replace them all with a single hyphen "-"
I remain at your disposal for any additional information.
Thank you in advance for your contributions.

Starting data :

Classeur1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Jeune	Père	Mère	Eleveur	Age	Volière	Cage	Né(e)	Tours	Information	Elevage
2	AETYTT-093/2012 M	AED27093/2012 M	27-093/2012 M	Gérard Claude	10a 11m 28j	2B	16	41068	4T
3	AE27094/2012 M	AE27094/2012 M	AET27 094/2012 M	Gérard Claude	10a 11m 28j	2B	16	41068	4T		X
4	AEY27 100/2012 F	AER27100/2012 F	E27100/2012 F	Gérard Claude	10a 11m 26j	2B	13	41070	4T
5	GAE27-059/2013 F	AEGG27-059/2013 F	AE27 059/2013 F	Gérard Claude	10a 1m 11j	3H	13	41389	4T		X
6	AE27060/2013 M	AE27060/2013 M	AE27060/2013 M	Gérard Claude	10a 0m 31j	5H	19	41399	5T
7	AUE27-087/2013 F	AE27 087/2013 F	AE-087/2013 F	Gérard Claude	10a 0m 18j	5H	22	41412	4T		X
8	AE27 011/2019 F	AEJ27011/2019 F	RG27 011/2019 F	Gérard Claude	4a 1m 14j	4H	11	43577	5T		X
9	AE27012/2019 F	AE27 012/2019 F	HT 012/2019 F	Gérard Claude	4a 1m 14j	5H	11	43577	5T		X
10	AE27-013/2019 M	AE27013/2019 M	AE27 013/2019 M	Gérard Claude	4a 1m 14j	4H	1	43577	4T		X
11	AE27 014/2019 F	AEG27014/2019 F	A014/2019 F	Gérard Claude	4a 1m 16j	4H	2	43575	4T
12	AEGYT 015/2019 M	G 015/2019 M	TT7 015/2019 M	Gérard Claude	4a 1m 17j	4H	6	43574	4T		X
Feuil1

Unless I am mistaken, here is the desired result :

Classeur11.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Jeune	Père	Mère	Eleveur	Age	Volière	Cage	Né(e)	Tours	Information	Elevage
2	AETYTT-093/2012 M	AED27-093/2012 M	27-093/2012 M	Gérard Claude	10a 11m 28j	2B	16	8-06-2012	4T
3	AE27-094/2012 M	AE27-094/2012 M	AET27-094/2012 M	Gérard Claude	10a 11m 28j	2B	16	8-06-2012	4T		X
4	AEY27-100/2012 F	AER27-100/2012 F	E27-100/2012 F	Gérard Claude	10a 11m 26j	2B	13	10-06-2012	4T
5	GAE27-059/2013 F	AEGG27-059/2013 F	AE27-059/2013 F	Gérard Claude	10a 1m 11j	3H	13	25-04-2013	4T		X
6	AE27-060/2013 M	AE27-060/2013 M	AE27-060/2013 M	Gérard Claude	10a 0m 31j	5H	19	5-05-2013	5T
7	AUE27-087/2013 F	AE27-087/2013 F	AE-087/2013 F	Gérard Claude	10a 0m 18j	5H	22	18-05-2013	4T		X
8	AE27-011/2019 F	AEJ27-011/2019 F	RG27-011/2019 F	Gérard Claude	4a 1m 14j	4H	11	22-04-2019	5T		X
9	AE27-012/2019 F	AE27-012/2019 F	HT-012/2019 F	Gérard Claude	4a 1m 14j	5H	11	22-04-2019	5T		X
10	AE27-013/2019 M	AE27-013/2019 M	AE27-013/2019 M	Gérard Claude	4a 1m 14j	4H	1	22-04-2019	4T		X
11	AE27-014/2019 F	AEG27-014/2019 F	A-014/2019 F	Gérard Claude	4a 1m 16j	4H	2	20-04-2019	4T
12	AEGYT-015/2019 M	G-015/2019 M	TT7-015/2019 M	Gérard Claude	4a 1m 17j	4H	6	19-04-2019	4T		X
Feuil1

 

[lrobbo314]

How about this?

Book2
	A	B
18	AETYTT-093/2012 M	AETYTT-093/2012 M
19	AE27094/2012 M	AE27-094/2012 M
20	AEY27 100/2012 F	AEY27-100/2012 F
21	GAE27-059/2013 F	GAE27-059/2013 F
22	AE27060/2013 M	AE27-060/2013 M
23	AUE27-087/2013 F	AUE27-087/2013 F
24	AE27 011/2019 F	AE27-011/2019 F
25	AE27012/2019 F	AE27-012/2019 F
26	AE27-013/2019 M	AE27-013/2019 M
27	AE27 014/2019 F	AE27-014/2019 F
28	AEGYT 015/2019 M	AEGYT-015/2019 M
Sheet4

Cell Formulas
Range	Formula
B18:B28	B18	=HYPE(A18)

Function HYPE(s As String)
Dim Matches As Object
Dim LP As String

With CreateObject("VBScript.RegExp")
    .Pattern = "(.+)(\d{3}\/.+)"
    Set Matches = .Execute(s)
    LP = Trim(Matches(0).submatches(0))
    If Right(LP, 1) <> "-" Then LP = LP & "-"
    HYPE = LP & Matches(0).submatches(1)
End With
End Function

 

[lrobbo314]

Here is a subroutine that should change all the values in columns A:C.

Sub France()
Dim r As Range:         Set r = Range("A2:C" & Range("A" & Rows.Count).End(xlUp).Row)
Dim AR() As Variant:    AR = r.Value2
Dim LP As String

With CreateObject("VBScript.RegExp")
    .Pattern = "(.+)(\d{3}\/.+)"
    For i = 1 To UBound(AR)
        For j = 1 To UBound(AR, 2)
            LP = Trim(.Replace(AR(i, j), "$1"))
            AR(i, j) = LP & IIf(Right(LP, 1) <> "-", "-", "") & .Replace(AR(i, j), "$2")
        Next j
    Next i
End With

r.Value2 = AR
End Sub

 

[harzer]

Hello lrobbo314.
Thank you for your reply.
The code gives me the right result, it is very fast and I am totally satisfied.
I tried to understand your code but given my level, it is impossible for me to decipher it.
Big THANKS to you.

 

[Peter_SSs]

Here is a short non-looping way that you could also consider. For the moment I have assumed the same amount of data in each of the three columns. If that is not the case an adjustment can be added.

I tried to understand your code ..

The logic of this code is

1. Replace all " " and "-" characters (including any correct ones) with nothing.
2. Insert a "-" before the 9th last character and insert a " " before the last character of the above result

Sub Fix_Format()
  With Range("A2", Range("C" & Rows.Count).End(xlUp))
    .Value = Evaluate("substitute(substitute(" & .Address & ","" "",""""),""-"","""")")
    .Value = Evaluate(Replace("replace(replace(#,len(#)-8,0,""-""),len(#)+1,0,"" "")", "#", .Address))
  End With
End Sub

 

[harzer]

Hello Peter_SSs,
Thanks for your proposition.
I tested it, it is very fast and gives the good result.
It's great to have expert people like you available to help newbies like me.
Thank you also for the explanations of the principle of operation of the code.
Cheers.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.