Extracting part of a cell
 

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

Extracting part of a cell
 

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.

Extracting part of a cell
 

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

Extracting part of a cell
 

=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

Extracting part of a cell
 

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.

Extracting part of a cell
 

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

Extracting part of a cell
 

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