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

harzer

Board Regular
Joined
Dec 15, 2021
Messages
148
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGHIJK
1JeunePèreMèreEleveurAgeVolièreCageNé(e)ToursInformationElevage
2AETYTT-093/2012 MAED27093/2012 M27-093/2012 MGérard Claude10a 11m 28j2B16410684T
3AE27094/2012 MAE27094/2012 MAET27 094/2012 MGérard Claude10a 11m 28j2B16410684TX
4AEY27 100/2012 FAER27100/2012 FE27100/2012 FGérard Claude10a 11m 26j2B13410704T
5GAE27-059/2013 FAEGG27-059/2013 FAE27 059/2013 FGérard Claude10a 1m 11j3H13413894TX
6AE27060/2013 MAE27060/2013 MAE27060/2013 MGérard Claude10a 0m 31j5H19413995T
7AUE27-087/2013 FAE27 087/2013 FAE-087/2013 FGérard Claude10a 0m 18j5H22414124TX
8AE27 011/2019 FAEJ27011/2019 FRG27 011/2019 FGérard Claude4a 1m 14j4H11435775TX
9AE27012/2019 FAE27 012/2019 FHT 012/2019 FGérard Claude4a 1m 14j5H11435775TX
10AE27-013/2019 MAE27013/2019 MAE27 013/2019 MGérard Claude4a 1m 14j4H1435774TX
11AE27 014/2019 FAEG27014/2019 FA014/2019 FGérard Claude4a 1m 16j4H2435754T
12AEGYT 015/2019 MG 015/2019 MTT7 015/2019 MGérard Claude4a 1m 17j4H6435744TX
Feuil1


Unless I am mistaken, here is the desired result :

Classeur11.xlsm
ABCDEFGHIJK
1JeunePèreMèreEleveurAgeVolièreCageNé(e)ToursInformationElevage
2AETYTT-093/2012 MAED27-093/2012 M27-093/2012 MGérard Claude10a 11m 28j2B168-06-20124T
3AE27-094/2012 MAE27-094/2012 MAET27-094/2012 MGérard Claude10a 11m 28j2B168-06-20124TX
4AEY27-100/2012 FAER27-100/2012 FE27-100/2012 FGérard Claude10a 11m 26j2B1310-06-20124T
5GAE27-059/2013 FAEGG27-059/2013 FAE27-059/2013 FGérard Claude10a 1m 11j3H1325-04-20134TX
6AE27-060/2013 MAE27-060/2013 MAE27-060/2013 MGérard Claude10a 0m 31j5H195-05-20135T
7AUE27-087/2013 FAE27-087/2013 FAE-087/2013 FGérard Claude10a 0m 18j5H2218-05-20134TX
8AE27-011/2019 FAEJ27-011/2019 FRG27-011/2019 FGérard Claude4a 1m 14j4H1122-04-20195TX
9AE27-012/2019 FAE27-012/2019 FHT-012/2019 FGérard Claude4a 1m 14j5H1122-04-20195TX
10AE27-013/2019 MAE27-013/2019 MAE27-013/2019 MGérard Claude4a 1m 14j4H122-04-20194TX
11AE27-014/2019 FAEG27-014/2019 FA-014/2019 FGérard Claude4a 1m 16j4H220-04-20194T
12AEGYT-015/2019 MG-015/2019 MTT7-015/2019 MGérard Claude4a 1m 17j4H619-04-20194TX
Feuil1
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
How about this?

Book2
AB
18AETYTT-093/2012 MAETYTT-093/2012 M
19AE27094/2012 MAE27-094/2012 M
20AEY27 100/2012 FAEY27-100/2012 F
21GAE27-059/2013 FGAE27-059/2013 F
22AE27060/2013 MAE27-060/2013 M
23AUE27-087/2013 FAUE27-087/2013 F
24AE27 011/2019 FAE27-011/2019 F
25AE27012/2019 FAE27-012/2019 F
26AE27-013/2019 MAE27-013/2019 M
27AE27 014/2019 FAE27-014/2019 F
28AEGYT 015/2019 MAEGYT-015/2019 M
Sheet4
Cell Formulas
RangeFormula
B18:B28B18=HYPE(A18)


VBA Code:
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
 
Upvote 0
Here is a subroutine that should change all the values in columns A:C.

VBA Code:
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
 
Upvote 0
Solution
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.
 
Upvote 0
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
VBA Code:
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
 
Upvote 1
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.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,099
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top