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


On Jan 31, 4:42*pm, LiveUser
wrote:
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

=TRIM(LEFT(A1,FIND(" ",A1)))
and
=TRIM(MID(A1,FIND(" ",A1),LEN(A1)))
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"LiveUser" wrote in message
...
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

Opps: change last formula to
=TRIM(SUBSTITUTE(MID(A2,FIND(" ",A2),LEN(A2)),"- ",""))
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"LiveUser" wrote in message
...
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

JP wrote:
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

I want it to look like this:

A B
1 001 ROTOR WILL NOT TURN

2 R05C22 TOTAL HUMAN-COMBUSTION



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)


"LiveUser" wrote:

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

Thank you Alan, I have to stop hitting 'Reply' before reading the
whole post.

--JP

On Jan 31, 7:51*pm, Alan Beban wrote:

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

Alan Beban