Extract part of a string

[bradjedis]

Hey folks,

Got a situation where I have a long string of data, and I need to remove the last 8 chars, and place them in the next cell over to the right.

Assume initial data is in column A and resultant location of the 8 chars is to be in column B.

chars are mixed , example being for the last 8 chars, 123aa123, however they are always in this format.

Thanks,
Brad

[HansV]

Would you like to use formulas, or do you prefer to use VBA code?

[bradjedis]

Hans,

VB works for me....


Thanks,
BRad

[HansV]

Try this:

Code: Select all

Sub SplitColumnA()
  Dim r As Long
  Dim m As Long
  Dim intLen As Integer
  Dim strVal As String
  m = Cells(Rows.Count, 1).End(xlUp).Row
  For r = 1 To m
    strVal = Cells(r, 1).Value
    intLen = Len(strVal) - 8
    If intLen < 0 Then
      intLen = 0
    End If
    Cells(r, 2) = Right(strVal, 8)
    Cells(r, 1) = Left(strVal, intLen)
  Next r
End Sub

[bradjedis]

Sweet!!!!


Thanks much

Brad