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

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.

Try,

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

Mike

" wrote:

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

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

--
HTH

Bob

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

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

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

" wrote:

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.

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

I would use DataText to ColumnsDelimited by hyphen

NextSelect the right hand column and "Do not import column-skip" and Finish


Gord Dibben MS Excel MVP

On Wed, 21 May 2008 03:41:34 -0700 (PDT), wrote:

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