Extracting part of a cell

[downs.ar]

Hi

I am trying to extract part of a cell's content, I understand how MID
LEFT etc. work but as far as I know they are all based on counting the
number of characters from a certain position within a cell.

What I am looking for is extracting the content of a cell upto a
specific character type e,g

Cell A2 = "HP DL380 G1-2-685"

I want to extract "HP DL380 G1" from the cell, in otherwords, upto the
the 1st hyphen.

The reason I cannot count the characters is that the data set has
mulitple hardware types of various character lenghts.

Is this possible, and if so how?


Many thanks
Andy

 

[downs.ar]

Think I may have somthing, if anyone has anything better please let me
know.

Thanks

OK I use:

=SEARCH("-",A2,1) to find the position of the 1st hyphen in cell A2

I then use:

=MID(F22,1,(BE22-1)) to extract the text upto the 1st hyphen, the -1
is to remove it.

 

[Mike H]

Try,

=TRIM(LEFT(A2,FIND("-",A2,1)-1))

Mike

 

[Bob Phillips]

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

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mike H]

Hi,

Assuming the formula

=SEARCH("-",A2,1)

is in BE22 then your formula
=MID(F22,1,(BE22-1)) will work but because you want the leftmost part it
would be better to use LEFT rather than MID and there is no need to put the
search formula in a seperate cell. I would also suggest you include the TRIM
option in my previous post to take care of any spaces at the start-end of the
extracted string.

Mike

 

[downs.ar]

Thanks all - I never knew about TRIM, I love this place

 

[Gord Dibben]

I would use Data>Text to Columns>Delimited by hyphen

Next>Select the right hand column and "Do not import column-skip" and Finish


Gord Dibben MS Excel MVP