Delete all text after 14th ^

SandsB

Well-known Member
Joined
Feb 13, 2007
Messages
705
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Fluff,
What is the significance of the (0) in RED (below)?

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

Tks,

Jim
 
Upvote 0
It simply returns the first element from the array created by the Split function
 
Upvote 0
VBA was requested, but there's also a formula solution:


Excel 2010
A
1Here'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.
2Here's^my^sample^text.^
3
4
5Here's^my^sample^text.^It^rambles^on^like^this^for^a^while^with^these^
6Here's^my^sample^text.^
Sheet5
Cell Formulas
RangeFormula
A5=IFERROR(LEFT(A1,FIND("|",SUBSTITUTE(A1,"^","|",14))),A1)
 
Last edited:
Upvote 0
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
 
Upvote 0
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]
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top