extracting text from a cell

[cmb269]

hello all,

i am trying to use the right and left function on my excel 2007. i don't know what i was doing wrong here. i want to extract the text string after the underscore:

HT-ALA851L-HARMK

HT-ALA851L-HARMK_LCD

HT-ALA851L-HARMK_2LCD

HT-ALA851L-HARMK_3LCD

HT-ALA851L-HARMK_CGV8

HT-ALA851L-HARMK_TCV8

HT-ALA851L-HARMK_BD1

HT-ALA851L-HARMK_BD2

HT-ALA851L-HARMK_BD3

HT-ALA851L-HARMK_BD4

HT-ALA851L-HARMK_BD5 LCD-ZTN9515-HC LCD-ZTN9515-HC_2LCD LCD-ZTN9515-HC_3LCD LCD-ZTN9515-HC_CGV8 LCD-ZTN9515-HC_TCV8 LCD-ZTN9515-HC_BD1 LCD-ZTN9515-HC_BD5 <colgroup><col></colgroup><tbody> </tbody>

<colgroup><col></colgroup><tbody>
</tbody>

i uses:

=RIGHT(A3,(FIND("_",A3)-1)) and =LEFT(A3,(FIND("_",A3)-1)) both did not work,

LA851L-HARMPARENT_LCD and HT-ALA851L-HARMPARENT

all i needed is the text after the underscore. "_LCD"

can someone guide me thru?


thanks
cmb269

 

[Andrew Poulsom]

Welcome to MrExcel.

Maybe:

=RIGHT(A3,LEN(A3)-FIND("_",A3))

 

[Lee.]

If the data starts in A1

=RIGHT(A1,LEN(A1)-FIND("_",A1))

Then copy down

 

[cmb269]

thanks Andrew and Lee,

it works, thank you very much guys.

cmb269

 

[Scott Huish]

Also:

=REPLACE(A1,1,FIND("_",A1&"_"),"")

 

[cmb269]

hello all, i have a related question. i am looking to extract the data in between the xx-xxxxx-xxx what function do i use?

HT-ALA851L-HARMK

HT-ALA851L-HARMK_LCD

HT-ALA851L-HARMK_2LCD

HT-ALA851L-HARMK_3LCD

HT-ALA851L-HARMK_CGV8

HT-ALA851L-HARMK_TCV8

HT-ALA851L-HARMK_BD1

HT-ALA851L-HARMK_BD2

HT-ALA851L-HARMK_BD3

HT-ALA851L-HARMK_BD4

HT-ALA851L-HARMK_BD5 all i need is ALA851L and below: LCD-ZTN9515-HC LCD-ZTN9515-HC_2LCD LCD-ZTN9515-HC_3LCD LCD-ZTN9515-HC_CGV8 LCD-ZTN9515-HC_TCV8 LCD-ZTN9515-HC_BD1 LCD-ZTN9515-HC_BD5 i would need ZTN9515..... if someone can help....... thanks cmb269 <tbody> </tbody>

<tbody>
</tbody>

 

[azumi]

=MID(A1,FIND("-",A1,1)+1,FIND("-",A1,FIND("-",A1,1)+1)-FIND("-",A1,1)-1)

or

=TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",99)),100,99))

 

[cmb269]

thanks azumi

the function is working.....

thanks
cmb269