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

Alfie101

New Member
Joined
Jan 29, 2016
Messages
19
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
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
38,452
Office Version
365
Platform
Windows
How about
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
Joined
Jan 29, 2016
Messages
19
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
Joined
Jun 12, 2014
Messages
38,452
Office Version
365
Platform
Windows
I'm not very good with formulae, but hopefully another member will be able to help.
 

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
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
Joined
Apr 18, 2011
Messages
35,800
Office Version
2010
Platform
Windows
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
Joined
Jan 29, 2016
Messages
19
Thank you Fluff, Ron and Rick.

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

Alfie101
 

Watch MrExcel Video

Forum statistics

Threads
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...
Top