Text to Columns using only first appearance of delimiter

[newshound12]

I would like to go Text to Columns using only the first - (dash)
that appears in the cell.

so if a cell contains PS2Y-PS/2 Y-Splitter
the resulting 2 cells will contain
PS2Y
PS/2 Y-Splitter

the characters before the first - (dash) vary so
I can't use fixed width.

 

[mtnbike_jr]

don't thint text to columns allows that. What I would do is to write formulas in the next two columns to the right of your list of unparsed data like this:

Cell B2: =left(a2,find("-",a2)-1)
Cell C2: =Right(a2,len(a2)-find("-",a2))
Assuming Col A was your list of unparsed data, and row 2 was the first record.

Copy those down to the end of your list. You could then copy,paste special > values over the formulas if you wish, then delete Col A.

 

[santeria]

I guess it all depends on how much data you are bringing in...

The Straight ahead - text bring on based on the - as the first delimiter will work if a way of Concatenating the additional cells is viable.

It seems that the Macro for the Event by Delimiting is

Sub TESTONEDELIMIT()
'
' TESTONEDELIMIT Macro
' Macro recorded 3/24/2004 '

'
    Workbooks.OpenText Filename:="M:\testonedelimiter.txt", Origin:=xlWindows, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
        , Space:=False, Other:=True, OtherChar:="-", FieldInfo:=Array(Array(1, 2 _
        ), Array(2, 1), Array(3, 1))
  End Sub

It would appear that the way to go is to edit something like this so it covers the events you require.... I'm not quite sure, but the Array sequence seems to be the key.

Just a thought.

 

[newshound12]

Thanks mtnbike_jr for the formula. It worked fine.
I had to use ISERROR for the cells that didn't contain a dash.

santeria,
I couldn't figure out what to do with your code.
The column is E
the sheet name is Main
the workbook name is DBE
Would you kindly help me out here?