Split text into 2 columns
 

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.

Split text into 2 columns
 

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
--
Gary''s Student - gsnu201001


"LeisaA" wrote:

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.

Split text into 2 columns
 

THANK YOU!!!!!!!!!!!! :)

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu201001


"LeisaA" wrote:

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.