Parsing a cell's value

[Bob Zimski]

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]

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

 

[Sam Wilson]

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"

 

[JoeU2004]

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 -----

 

[brzak]

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

 

[Patrick Molloy]

if there are spaces before then the formula should be

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

 

[Rick Rothstein]

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.