CODEWITHSUNDEEP
vbaexcel
user1787114
user1787114

Reputation: 205

Removing part of a string

I have this code, that works 90-95% of the time, but once in a while it will only delete part of the piece that I want deleted.

While InStr(fnd, SCHFR) <= 0 And c <= 300
  c = c + 1
  fnd = Sheet1.Cells(c, 8)
Wend
  If c >= 300 Then
    Sheet2.Cells(r, cmn) = "Not found"
  Else
    cleanup = InStr(1, fnd, ": ")
    finalString = Right(fnd, Len(fnd) - cleanup - 1)
    Sheet2.Cells(r, cmn) = finalString
  End If
  scnm = scnm + 1
  cmn = cmn + 1
  c = 1
Wend

scnm is just the counter to keep track of what im seraching for in the string to clean it up. SCHFR is defined like this:

Select Case scnm
  Case 1
    SCHFR = "First Name:"
  Case 2
    SCHFR = "Last Name:"
  Case 3
    SCHFR = "Phone:"
  Case 4
    SCHFR = "Seconday Phone:"
  Case 5
    SCHFR = "Email:"
  Case 6
    SCHFR = "SecondaryEmail:"
  Case 7
    SCHFR = "Country:"
End Select

The results come back like "First Name: Sarah", and I want to remove the "First Name: " part so its just "Sarah", but occasionally I'll get a cell that has something like "rst Name: Sarah" instead, while all the others on the sheet around it are correct.

Upvotes: 0

Views: 147

Answers (2)

user8608712
user8608712

Reputation:

This simple code worked great for me (supposing you have "First Name: Sarah" in column A; it Shows just "Sarah" on column B) :

Sub remove_until()

Dim i, lrowA, remChar As Long
Dim mString As String

lrowA = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To lrowA

mString = Cells(i, 1).Value


        remChar = InStr(mString, ":") + 1

        Cells(i, 2).Value = Right(mString, Len(mString) - remChar)



Next

End Sub

Upvotes: 0

Jean-Pierre Oosthuizen
Jean-Pierre Oosthuizen

Reputation: 2683

Why not use an InStr to find the index for the : and then use that to remove everything on the left of that position.

Example Data:

enter image description here

Sub SomeSub()

    Dim MyRange As Range
    Dim index As Integer
    Dim CellValue As String
    Dim CapturedValue As String

    Set MyRange = ThisWorkbook.ActiveSheet.UsedRange.Columns(1)

    For Each cell In MyRange.Cells

        CellValue = cell.Value

        ' +1 caters for the sapce between the : and then data
        index = InStr(1, CellValue, ":") + 1

        If Len(CellValue) > index Then

            CapturedValue = Right(CellValue, Len(CellValue) - index)

        Else
            'No Data has been inseted after the :

            CapturedValue = ""
        End If

        cell.Value = CapturedValue

    Next cell

End Sub

Upvotes: 1

Related Questions