VBA code for scan cell and do line break before specific letter sequence.

[JAZ91]

Hi,

I have a macro I would like to make but I am not very proficient in VBA coding and are having a hard time finding anything similar that i could tweak to work.

What I would like to do is to scan a column and then do a line break before a specific sequence of letters.
Secondarily I would also (if possible) like to add "-" between the equipment type.

Here is a little explanation of the data and to add some context.
The first 3 numbers and the first letter designate the process module. The next letter(s) are the type of equipment. The next numbers are the specific unit.
When all this is combined it makes up a TAG number. The letters DI/DO/AO/AI are the signal type. And then the remaining part is the RTU and the number of that specific RTU along with the IO channel which the TAG number is connected up to.

Finally, if I could then wrap the cells so i have 2 lines with final data it would be perfect.
This goal is to make cable labels easily from an imported file from the control system I'm working with.


i.e. my data looks like this.

120AV199_DI RTU12.2.4 / 01.05

240AFT209_DO RTU15.3.8 / 02.06

770AV405_AO RTU10.7.2 / 05.09

820ALSH304_AI RTU10.5.9 / 07.03

i.e. how I want the result to look

120A-V-199_DI
RTU12.2.4 / 01.05

240A-FT-209_DO
RTU15.3.8 / 02.06

770A-V-405_AO
RTU10.7.2 / 05.09

820A-LSH-304_AI
RTU10.5.9 / 07.03

 

[aaewalsh]

Try This:

Sub format_data()
    Dim rng As Range, cel As Range

    Set rng = Selection

    For Each cel In rng
        'Add line break before "_"
        cel.Value = Replace(cel.Value, "_", vbCrLf & "_")
        'Add "-" between equipment type
        cel.Value = Replace(cel.Value, "AV", "A-V")
        cel.Value = Replace(cel.Value, "FT", "F-T")
        cel.Value = Replace(cel.Value, "LSH", "L-S-H")
        'Wrap the text in the cell
        cel.WrapText = True
    Next cel
End Sub

You can run this macro by selecting the column of data you want to format and then running the macro.

Note that this code uses the Replace function to add the line break and the "-" characters in the appropriate places. The WrapText property is set to True to wrap the text in the cell.

You can add more Replace function if you have more equipment type, also if you have different format of the data you can edit the Replace function accordingly.