Delete all text after 14th ^

SandsB

Well-known Member
Joined
Feb 13, 2007
Messages
603
Let's say you have a file with a column of text. Within that text are some ^ symbols. Something like this:

Here's^my^sample^text.^It^rambles^on^like^this^for^a^while^with^these^stupid^symbols^in^there^and^a^macro^does^a^Text to Columns^to^put^the^data^where^it^needs^to^go.

I need to get rid of all text after the 14th ^ leaving me with (in the example above) nothing but:

Here's^my^sample^text.^It^rambles^on^like^this^for^a^while^with^these^

The data file has several hundred rows and the text between the ^s will be different in all of them. Almost all of the cells in question will have 14 ^s. Some of the cells in this column will be (and should be) completely blank. Bad typing by the person entering data could mean that some of these cells have more or fewer ^s. If fewer - leave as is. If more, delete everything after the 14th ^.

How can I do that in VBA?
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

veyselemre

Board Regular
Joined
Mar 16, 2006
Messages
55
Code:
Sub test()    
    Dim bl, i As Integer
    For i = 1 To Cells(Rows.Count, 1).End(3).Row
        bl = Split(Cells(i, 1).Value, "^")
        If UBound(bl) > 13 Then
            ReDim Preserve bl(0 To 13)
            Cells(i, 2).Value = Join(bl, "^") & "^"
        Else
            Cells(i, 2).Value = Cells(i, 1).Value
        End If
    Next i
End Sub
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,134
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
Sub SandsB()
   Dim Cl As Range
   For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
      If Cl.Value <> "" Then
         Cl.Offset(, 1) = Split(Application.Substitute(Cl, "^", "|", 14), "|")(0)
      End If
   Next Cl
End Sub
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,529
This alternative will return the values to the same column (column A) without a helper column:
Code:
Sub delText()
    Application.ScreenUpdating = False
    Dim LastRow As Long, rng As Range
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each rng In Range("A1:A" & LastRow)
        If InStr(1, rng, "^") > 0 And Len(rng) - Len(WorksheetFunction.Substitute(rng, "^", "")) > 14 Then
            rng = Left(rng, WorksheetFunction.Find("|", WorksheetFunction.Substitute(rng, "^", "|", 14)))
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub
 

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,471

ADVERTISEMENT

Fluff,
What is the significance of the (0) in RED (below)?

Split(Application.Substitute(Cl, "^", "|", 14), "|")(0)

Tks,

Jim
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,134
Office Version
  1. 365
Platform
  1. Windows
It simply returns the first element from the array created by the Split function
 

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,117

ADVERTISEMENT

VBA was requested, but there's also a formula solution:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Here's^my^sample^text.^It^rambles^on^like^this^for^a^while^with^these^stupid^symbols^in^there^and^a^macro^does^a^Text to Columns^to^put^the^data^where^it^needs^to^go.</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Here's^my^sample^text.^</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Here's^my^sample^text.^It^rambles^on^like^this^for^a^while^with^these^</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Here's^my^sample^text.^</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet5</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A5</th><td style="text-align:left">=IFERROR(<font color="Blue">LEFT(<font color="Red">A1,FIND(<font color="Green">"|",SUBSTITUTE(<font color="Purple">A1,"^","|",14</font>)</font>)</font>),A1</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
Here is another macro to consider...
Code:
Sub SandsB()
  Dim Cell As Range, Arr() As String
  On Error Resume Next
  For Each Cell In Range("A2", Cells(Rows.Count, "A").End(xlUp))
    Arr = Split(Cell, "^", 15)
    Arr(14) = ""
    Cell = Join(Arr, "^")
  Next
  On Error GoTo 0
End Sub
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
Here is another macro to consider...
Code:
Sub SandsB()
  Dim Cell As Range, Arr() As String
  On Error Resume Next
  For Each Cell In Range("A2", Cells(Rows.Count, "A").End(xlUp))
    Arr = Split(Cell, "^", 15)
    Arr(14) = ""
    Cell = Join(Arr, "^")
  Next
  On Error GoTo 0
End Sub
And here is how to do it without the loop...
Code:
[table="width: 500"]
[tr]
	[td]Sub SandsB()
  With Range("A2", Cells(Rows.Count, "A").End(xlUp))
    .Value = Evaluate(Replace("IFERROR(LEFT(@,FIND(""|"",SUBSTITUTE(@,""^"",""|"",14))),@)", "@", .Address))
  End With
End Sub[/td]
[/tr]
[/table]
 

SandsB

Well-known Member
Joined
Feb 13, 2007
Messages
603
So many options to fix this problem. That's why this site is the best source of Excel training.

This particular option is the one I tried and it worked great. Thanks to everyone.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,732
Messages
5,524,513
Members
409,583
Latest member
RedHelp

This Week's Hot Topics

Top