Need to seperate

  • Thread starter Thread starter LiveUser
  • Start date Start date
L

LiveUser

Okay I am doing something wrong here.


I have a little over 350 lines of information that look something like this:

001 - ROTOR WILL NOT TURN

I need to split the 001 from ROTOR WILL NOT TURN and get rid of the hyphen
altogther.

The problem is not all 001's are the same length, for instance, it could be
R05C22 TOTAL HUMAN-COMBUSTION.

So, I can't do a fixed width because it cuts out some of the data and the
other way seperates every hyphen.

I want it to look like this:


A B
1 001 ROTOR WILL NOT TURN

2 R05C22 TOTAL HUMAN-COMBUSTION
 
Is there a hyphen between the numbers and text in each cell? If so,
you can use 'Text to Columns' and specify the hyphen as the delimiter.

1. Select the cells and go to Data menu, Text to Columns
2. Choose 'Delimited' and click Next
3. In the 'Delimiters' box click Other and enter a hyphen in the box.
4. Click Finish.


HTH,
JP
 
=TRIM(LEFT(A1,FIND(" ",A1)))
and
=TRIM(MID(A1,FIND(" ",A1),LEN(A1)))
best wishes
 
Opps: change last formula to
=TRIM(SUBSTITUTE(MID(A2,FIND(" ",A2),LEN(A2)),"- ",""))
 
JP said:
Is there a hyphen between the numbers and text in each cell? If so,
you can use 'Text to Columns' and specify the hyphen as the delimiter.

1. Select the cells and go to Data menu, Text to Columns
2. Choose 'Delimited' and click Next
3. In the 'Delimiters' box click Other and enter a hyphen in the box.
4. Click Finish.


HTH,
JP
Click to expand...

That doesn't produce what the OP wants it to look like.

Alan Beban
 
=LEFT(A1,FIND(" ",A1)-1)
and
=MID(SUBSTITUTE(A1," - "," "),FIND(" ",SUBSTITUTE(A1," - "," "))+1,99)
 
Back
Top