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

#### Alfie101

##### New Member
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

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### Fluff

##### MrExcel MVP, Moderator
Code:
``````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.

Last edited:

#### Alfie101

##### New Member
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

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

#### Ron Coderre

##### MrExcel MVP
With your sample data in A1:A8...Try this formula (copied down)
Code:
``````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

##### MrExcel MVP
With your sample data in A1:A8...Try this formula (copied down)
Code:
``````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

##### New Member
Thank you Fluff, Ron and Rick.

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

Alfie101

1,095,195
Messages
5,442,965
Members
405,209
Latest member
jann6628

### This Week's Hot Topics

• Copy entire row if CountA <>0 to another sheet
[B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
• Select last used Row in Table
I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
• excel workbook: do not allow certain file name
Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
• fixing problem autofilter
hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
• “Weight”
Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
• How to capitalize everything before a certain character?
In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...