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 :
Unless I am mistaken, here is the desired result :
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 |