How to delete sub-strings of variable length in middle of larger strings

[Alfie101]

Hi, I have many thousands of records like the below:

128K.1381.122.2.A.8.51856273-2753672-113

128K.1459.111.128.A.8.51769315-2753672-113

128K.1459.111.2.A.8.51467685-2753672-113

130E.1456.122.28.A.8.51543816-2060102-1

130E.1456.122.32.T.8.51543816-2060102-1

130E.1456.122.64.A.8.51543816-2060102-1

130E.1567.100.124.A.8.51331082-2060102-1

<tbody>
</tbody>

146J.1381.122.128.T.8.51848388-2753672-113

I want to be able to delete the fourth sub-string of the seven total in each case, and preserve the rest of the contents of each record. That is, in the above examples I want to be able to delete these: .2, .128, .2, .28, .32, . 64, .124, and .128
And have resulting records like these:

128K.1381.122.A.8.51856273-2753672-113

128K.1459.111.A.8.51769315-2753672-113

128K.1459.111.A.8.51467685-2753672-113

130E.1456.122.A.8.51543816-2060102-1

130E.1456.122.T.8.51543816-2060102-1

130E.1456.122.A.8.51543816-2060102-1

130E.1567.100.A.8.51331082-2060102-1

<tbody>
</tbody>

146J.1381.122.T.8.51848388-2753672-113

I would be most grateful for your help, thank you.
Alfie101

 

[Fluff]

How about

Sub splitdata()
   Dim cl As Range
   Dim sp As Variant
   
   For Each cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
      sp = Split(cl.Value, ".")(3)
      cl.Replace "." & sp & ".", ".", xlPart, , , , False, False
   Next cl
End Sub

EDIT:
If there is a chance that the second & fourth sub-strings match then this won't work.

 

[Alfie101]

Hi Fluff,
Thank you. Is there any chance the same result can be via formula(s)? I am not too good on VBA, years ago but not for yonks.
Appreciated,
Alfie101

 

[Fluff]

I'm not very good with formulae, but hopefully another member will be able to help.

 

[Ron Coderre]

With your sample data in A1:A8...Try this formula (copied down)

B1: =SUBSTITUTE(LEFT(SUBSTITUTE(A1,".","."&REPT(" ",LEN(A1))),LEN(A1)*3)
&RIGHT(SUBSTITUTE(A1,".","."&REPT(" ",LEN(A1))),LEN(A1)*3)," ","")

Is that something you can work with?

 

[Rick Rothstein]

With your sample data in A1:A8...Try this formula (copied down)

B1: =SUBSTITUTE(LEFT(SUBSTITUTE(A1,".","."&REPT(" ",LEN(A1))),LEN(A1)*3)
&RIGHT(SUBSTITUTE(A1,".","."&REPT(" ",LEN(A1))),LEN(A1)*3)," ","")

Is that something you can work with?

I think you may have something wrong in your formula as I get it to delete both the 4th and 5th fields (the fifth field being the letter after the fourth field). With that said, here is an alternate formula that I believe works correctly...

=LEFT(A1,FIND("|",SUBSTITUTE(A1,".","|",3)))&MID(A1,FIND("|",SUBSTITUTE(A1,".","|",4))+1,99)

 

[Alfie101]

Thank you Fluff, Ron and Rick.

I have used your formula Rick. Works really well thank you.

Alfie101