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
Extract part of a string
-
- 4StarLounger
- Posts: 550
- Joined: 30 Mar 2010, 18:49
- Location: United States
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Extract part of a string
Would you like to use formulas, or do you prefer to use VBA code?
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 550
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Extract part of a string
Hans,
VB works for me....
Thanks,
BRad
VB works for me....
Thanks,
BRad
-
- Administrator
- Posts: 78665
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Extract part of a string
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
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 550
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Extract part of a string
Sweet!!!!
Thanks much
Brad
Thanks much
Brad