I have a cell which contains a part number and a revision level in the format
'ARD010 Rev: 2'. I need to drop the 'Rev: 2' portion in the cell to the
right and the existing cell needs to end up with 'ARD010' without any spaces.

Any help appreciated.

Bob

I forgot to mention that the part number can vary in length which is what is
causing me trouble.

"Bob Zimski" wrote:

I have a cell which contains a part number and a revision level in the format
'ARD010 Rev: 2'. I need to drop the 'Rev: 2' portion in the cell to the
right and the existing cell needs to end up with 'ARD010' without any spaces.

Any help appreciated.

Bob

You could use text to columns - Highlight the column and go to data/text to
columns.

From there choose delimited, and specify (space) as your delimiter from the
second screen, making sure you tick "treat consecutive delimiters as one"

"Bob Zimski" wrote:

I forgot to mention that the part number can vary in length which is what is
causing me trouble.

"Bob Zimski" wrote:

I have a cell which contains a part number and a revision level in the format
'ARD010 Rev: 2'. I need to drop the 'Rev: 2' portion in the cell to the
right and the existing cell needs to end up with 'ARD010' without any spaces.

Any help appreciated.

Bob

"Bob Zimski" wrote:
I need to drop the 'Rev: 2' portion in the cell to the right and the
existing cell needs to end up with 'ARD010' without any spaces.

Ostensibly, try:

=left(A1,find(" ",A1)-1)

That assumes there are no spaces __before__ the part number. Alternatively:

=trim(left(A!,find(" ",A1)-1))

Both assume that the white space between part number and revision are true
blanks. If the above formula does not work, post back here to solve the
problem.


------ original message -----

"Bob Zimski" wrote in message
...
I have a cell which contains a part number and a revision level in the
format
'ARD010 Rev: 2'. I need to drop the 'Rev: 2' portion in the cell to
the
right and the existing cell needs to end up with 'ARD010' without any
spaces.

Any help appreciated.

Bob

if the contents are always in the same format, i.e. a combination of
letters and numbers only, followed by spaces, folowed by 'Rev #', then
a formula would suffice

If cell A1 contents are (no quotes): 'ARD010 Rev: 2'

you could use the formula:

=LEFT(A1,FIND(" ",A1)-1)

resulting in

'ARD010'

brz


On 22 June, 15:55, Bob Zimski
wrote:
I forgot to mention that the part number can vary in length which is what is
causing me trouble.



"Bob Zimski" wrote:
I have a cell which contains a part number and a revision level in the format
'ARD010 * * Rev: 2'. I need to drop the 'Rev: 2' portion in the cell to the
right and the existing cell needs to end up with 'ARD010' without any spaces.

Any help appreciated.

if there are spaces before then the formula should be

=left( TRIM(A1) ,find(" ",TRIM(A1) )-1)

"JoeU2004" wrote in message
...
"Bob Zimski" wrote:
I need to drop the 'Rev: 2' portion in the cell to the right and the
existing cell needs to end up with 'ARD010' without any spaces.

Ostensibly, try:

=left(A1,find(" ",A1)-1)

That assumes there are no spaces __before__ the part number.
Alternatively:

=trim(left(A!,find(" ",A1)-1))

Both assume that the white space between part number and revision are true
blanks. If the above formula does not work, post back here to solve the
problem.


------ original message -----

"Bob Zimski" wrote in message
...
I have a cell which contains a part number and a revision level in the
format
'ARD010 Rev: 2'. I need to drop the 'Rev: 2' portion in the cell to
the
right and the existing cell needs to end up with 'ARD010' without any
spaces.

Any help appreciated.

Bob

Assuming the revision level always starts with "Rev:" (in that letter
casing), you could do this...

=TRIM(LEFT(A1,FIND("Rev:",A1&"Rev:")-1))

If the "Rev" is not always in that letter casing (that is, maybe sometimes
it is "rev"), the replace FIND with SEARCH.

--
Rick (MVP - Excel)


"Bob Zimski" wrote in message
...
I have a cell which contains a part number and a revision level in the
format
'ARD010 Rev: 2'. I need to drop the 'Rev: 2' portion in the cell to
the
right and the existing cell needs to end up with 'ARD010' without any
spaces.

Any help appreciated.

Bob