Parsing a cell's value
 

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

Parsing a cell's value
 

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

Parsing a cell's value
 

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

Parsing a cell's value
 

"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

Parsing a cell's value
 

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.

Parsing a cell's value
 

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

Parsing a cell's value
 

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