Text to Columns

[LeisaA]

500+ sentences, I need to seperate (text to columns) after the first "-"
only. There are subsequent "-" but I only want it seperated at the first.
And when I seperate it in the second column I do not want a space preceeding
the text. Can you give advice on this, any help would be much appreciated!
Thanks

Sample Data

AIR TAXI - An aircraft operator who conducts operations for hire or
compensation in accordance with FAR Part 135 in an aircraft with 30 or fewer
passenger seats and a payload capacity of 7,500# or less. An air taxi
operates on an on-demand basis and does not meet the "flight scheduled"
qualifications of a commuter.
AIR TRAFFIC CONTROL (ATC) - A service operated by the appropriate authority
to promote the safe, orderly, and expeditious flow of air traffic.
AIRPORT TRAFFIC CONTROL TOWER (ATCT) - A terminal facility that uses
air/ground communications, visual signaling, and other devices to provide ATC
services to aircraft operating in the vicinity of an airport or on the
movement area. Authorizes aircraft to land or takeoff at the airport
controlled by the tower or to transit the Class D airspace area regardless of
flight plan or weather conditions (IFR or VFR). A tower may also provide
approach control services (radar or non-radar).
ALCLAD - Trademark name of Alcoa for high-strength sheet aluminum clad with
a layer (approximately 5.5% thickness per side) of high-purity aluminum,
popularly used in airplane manufacture.
ALPHABET (PHONETIC) - Devised for reasons of clarity in aviation voice
radio, this is the current NATO version in global use:

 

[Rick Rothstein]

You could use this macro (just set the starting cell and column letters as
needed in the For Each statements Range reference)...

Sub SplitOnFirstDash()
Dim Cell As Range, Parts() As String
For Each Cell In Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
Parts = Split(Cell.Value, "-", 2)
Cell.Offset(0, 1).Value = Trim(Parts(1))
Cell.Value = Trim(Parts(0))
Next
End Sub

 

[Dave Peterson]

Check one of your other posts.

500+ sentences, I need to seperate (text to columns) after the first "-"
only. There are subsequent "-" but I only want it seperated at the first.
And when I seperate it in the second column I do not want a space preceeding
the text. Can you give advice on this, any help would be much appreciated!
Thanks

Sample Data

AIR TAXI - An aircraft operator who conducts operations for hire or
compensation in accordance with FAR Part 135 in an aircraft with 30 or fewer
passenger seats and a payload capacity of 7,500# or less. An air taxi
operates on an on-demand basis and does not meet the "flight scheduled"
qualifications of a commuter.
AIR TRAFFIC CONTROL (ATC) - A service operated by the appropriate authority
to promote the safe, orderly, and expeditious flow of air traffic.
AIRPORT TRAFFIC CONTROL TOWER (ATCT) - A terminal facility that uses
air/ground communications, visual signaling, and other devices to provide ATC
services to aircraft operating in the vicinity of an airport or on the
movement area. Authorizes aircraft to land or takeoff at the airport
controlled by the tower or to transit the Class D airspace area regardless of
flight plan or weather conditions (IFR or VFR). A tower may also provide
approach control services (radar or non-radar).
ALCLAD - Trademark name of Alcoa for high-strength sheet aluminum clad with
a layer (approximately 5.5% thickness per side) of high-purity aluminum,
popularly used in airplane manufacture.
ALPHABET (PHONETIC) - Devised for reasons of clarity in aviation voice
radio, this is the current NATO version in global use:

 

[Wouter HM]

Hi LeisaA

In Excel 2007 I used the nested cell fuction below to get everything
rigt of the first dash.

=MID(A1,SEARCH("-",A1,1)+2,LEN(A1))

You can olso use the function below:


Function RightOfFirst(strOriginal As String, strDelimiter) As String
Dim intPos As Integer

intPos = InStr(1, strOriginal, strDelimiter, vbTextCompare) + 2
If intPos > 0 Then
RightOfFirst = Mid(strOriginal, intPos)
Else
RightOfFirst = strOriginal
End If

End Function


Function LeftOfFirst(strOriginal As String, strDelimiter) As String
Dim intPos As Integer

intPos = InStr(1, strOriginal, strDelimiter, vbTextCompare) + 2
If intPos > 0 Then
LeftOfFirst = Left(strOriginal, InpOs)
Else
LeftOfFirst = strOriginal
End If

End Function



HTH,

Wouter.

 

[Gary''s Student]

Here is a sample macro for column A split into A & B:

Sub SplitThem()
Set r = Intersect(Range("A:A"), ActiveSheet.UsedRange)
For Each rr In r
v = rr.Value
n = InStr(v, "-")
rr.Value = Left(v, n - 1)
rr.Offset(0, 1).Value = Trim(Mid(v, n + 1, 9999))
Next
End Sub

 

[Rick Rothstein]

rr.Offset(0, 1).Value = Trim(Mid(v, n + 1, 9999))

Two things about about the above line of code....

First, you can remove the Trim function call since you accounted for the
leading space when you used n+1 for the Mid function's second argument.
Second, you can remove the 9999 (third) argument from the Mid function
call... unlike the worksheet function's MID function, in the VBA one the
third argument is optional and, when you omit it, it automatically returns
all the remaining chars (after the position specified by the second
argument).

 

[Gary''s Student]

Thanks for the info!

 

[LeisaA]

Two things about about the above line of code....

First, you can remove the Trim function call since you accounted for the
leading space when you used n+1 for the Mid function's second argument.
Second, you can remove the 9999 (third) argument from the Mid function
call... unlike the worksheet function's MID function, in the VBA one the
third argument is optional and, when you omit it, it automatically returns
all the remaining chars (after the position specified by the second
argument).

--
Rick (MVP - Excel)




.
Sorry, I am not understanding how to use this function?