Split text into 2 columns

  • Thread starter Thread starter LeisaA
  • Start date Start date
L

LeisaA

Separate my data into two columns at the first Capital letter?

My data looks like this x 544 lines

aerodrome A defined area on land or water.
air cargo Freight, mail and express traffic transported by air.
airport An area of land or water that is used or intended to be used for
landing an aircraft.
 
Here is an example splittin col A into cols A & B:

Sub capSplit()
Dim v As String, s As String
Set r = Intersect(ActiveSheet.UsedRange, Range("A:A"))
For Each rr In r
v = rr.Value
For i = 1 To Len(v)
s = Mid(v, i, 1)
If Asc(s) > 64 And Asc(s) < 91 Then
rr.Value = Left(v, i - 1)
rr.Offset(0, 1).Value = Mid(v, i)
Exit For
End If
Next
Next
End Sub
 
THANK YOU!!!!!!!!!!!! :)

Gary''s Student said:
Here is an example splittin col A into cols A & B:

Sub capSplit()
Dim v As String, s As String
Set r = Intersect(ActiveSheet.UsedRange, Range("A:A"))
For Each rr In r
v = rr.Value
For i = 1 To Len(v)
s = Mid(v, i, 1)
If Asc(s) > 64 And Asc(s) < 91 Then
rr.Value = Left(v, i - 1)
rr.Offset(0, 1).Value = Mid(v, i)
Exit For
End If
Next
Next
End Sub
Click to expand...
 
Back
Top