Delete "." at the beginning and end of a string

[Chris101]

I am new at VBA So please bear with me.

I download each month a workbook that contains in one column a list of circuit id's that needs to be imported into an access database. My issue is that the circuit Id's in excel should look like the following:
MMEC.904189..ATI or IUEC.707657..ATI etc. The characters before the first "." is always four and letters. The numbers after the first "." is always six and the letters after the ".." is always ATI and three.

As of late when I download the workbook the circuit ID's format has changed to: .MLEC.793780..ATI. or .BREC.878099..ATI. . I cannot have a leading "." before the letters and an ending "." after ATI. There are some circuit Id's that are correct like in the first example but many of the circuit id's in the column are not in the format needed to import into my database.

I would like to skip over those circuit id's that are correct like in the first format but remove the leading "." and ending "." . The "." after the lettrs and the ".." period before the "ATI are needed. For example, .MLEC.793780..ATI. should look like MLEC.793780..ATI

I have attached a sample pic of the data for better understanding. I am trying to figure out how to attach a spreadsheet.

I thought about something like the below code where I added "." in the circuit Id's but I have not been successful in saying if there is a leading "." then remove it and then look for a "." after ATI and remove the "." if it is present.

Dim k As String

For Each rng In Range("k2", Range("k1").End(xlDown))

k = rng.Value
k = Replace(s, " ", ".", InStr(1, s, ".") + 1, 1)
k = Replace(s, " ", "..")
rng.Value = s
Next rng

Thank you in advance for your time and assistance. I very much appreciate any feedback.

Best Regards,
Chris

 

[Rick Rothstein]

Maybe this inside your For..Next instead of what you are showing...

If rng.Value Like ".*" Then rng.Value = Mid(rng.Value, 2)
If rng.Value Like "*." Then rng.Value = Left(rng.Value, Len(rng.Value) -1)

 

[Chris101]

Rick,

Your suggestion worked like a charm.

I really appreciate your time and effort.

You are my go-to guy for now on.

Thanks again,
Chris