Adding a space at every fourth letter using VBA

[Chris101]

I have a list of circuit ID's that need to be formatted the same way. When I receive the circuit ID's they need to be in the format - "MMEC.123456..ATI" . The circuit IDs are now arriving as "MMEC123456..ATI". There is no "." after the letter "C". I would like to place a "." after the fourth letter of every circuit ID in a column. There will always be a "." after the fourth letter.

I attached a screen shot of the data for review, if necessary.

I can spend hours looking at the various solutions, but still get nowhere. If anyone would be gracious enough to assist me with my issue, I would greatly appreciate it.

Thank you in advance for your time and knowledge.

Best Regards,
Chris

 

[kevin9999]

There's probably simpler ways of doing it, but try this on a copy of your data.

Sub AddDot()
    With Sheets("DB Load").Range("S2:S" & Sheets("DB Load").Cells(Rows.Count, "S").End(xlUp).Row)
        .Value = Evaluate("LEFT(" & .Address(, , , 1) & ",4)&"".""&RIGHT(" & .Address(, , , 1) & ",LEN(" & .Address(, , , 1) & ")-4)")
    End With
End Sub

 

[Chris101]

Kevin9999,

I want to thank you for your assistance. It worked perfectly.

I think I would have spent a week trying to figure out what you did and probably still not being able to get it correct.

What do you recommend as a good learning tool for a beginner to learn VBA? favorite Book, YouTube channel, class, etc.? I spent a long time on YouTube looking for your solution and could not find any that worked.

Again, I really appreciate you sharing your knowledge and time with me. Thank You!

Best Regards,
Chris

 

[kevin9999]

Actually Chris, the following code is safer - insofar as the previous code will add yet another period if you run it a second time, whereas the code below will not.

Sub AddDot()
    With Sheets("DB Load").Range("S2:S" & Sheets("DB Load").Cells(Rows.Count, "S").End(xlUp).Row)
        .Value = Evaluate("if(mid(" & .Address(, , , 1) & ",5,1) <> ""."",LEFT(" & .Address(, , , 1) & ",4)&"".""&RIGHT(" & .Address(, , , 1) & ",LEN(" & .Address(, , , 1) & ")-4)," & .Address(, , , 1) & ")")
    End With
End Sub

 

[kevin9999]

What do you recommend as a good learning tool for a beginner to learn VBA?

There's a ton of free resources on the web. Here's just one of many starting points that I find useful still:
https://www.youtube.com/c/SiddharthRout/videos

 

[Chris101]

Actually Chris, the following code is safer - insofar as the previous code will add yet another period if you run it a second time, whereas the code below will not.

Sub AddDot()
    With Sheets("DB Load").Range("S2:S" & Sheets("DB Load").Cells(Rows.Count, "S").End(xlUp).Row)
        .Value = Evaluate("if(mid(" & .Address(, , , 1) & ",5,1) <> ""."",LEFT(" & .Address(, , , 1) & ",4)&"".""&RIGHT(" & .Address(, , , 1) & ",LEN(" & .Address(, , , 1) & ")-4)," & .Address(, , , 1) & ")")
    End With
End Sub

I tried the safer code and I received the error #VALUE!

 

[Chris101]

There's a ton of free resources on the web. Here's just one of many starting points that I find useful still:
https://www.youtube.com/c/SiddharthRout/videos

I will definitely check the link out. Thank you,

 

[kevin9999]

I tried the safer code and I received the error #VALUE!

Strange, worked fine for me?

 

[Chris101]

I am sure I did something wrong. I copied and pasted it maybe that was the problem.

 

[Chris101]

It is working great! I do appreciate your help.

Thank You again.

Chris