len & if function

[ATHGR]

Hi Guys,

I have a question about excel.

i would like to create a function with this criteria : when the wbs is GWV1P100275-01 until GWV1P100275-09 i would like to take in "I" cell the value GWV1P100275 , when the wbs is GWV1P100275-01-001 until GWV1P100275-01-009 i would like to take in "I' cell the value GWV1P100275-01 , when the wbs is GWV1P100275-02-001 until GWV1P100275-02-009 i would like to take in "I" cell the value GWV1P100275-02

thanks in advance!

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IF(ISNUMBER(FIND("-",F2)),LEFT(F2,FIND("-",F2)-1),F2)

 

[Alex Blakenburg]

I think you might want something like this.

Book1
	F	G	H	I
1	Original WBS			Parent
2	GWV1P100275
3	GWV1P100275-01			GWV1P100275
4	GWV1P100275-01-001			GWV1P100275-01
Sheet1

Cell Formulas
Range	Formula
I2:I4	I2	=IFERROR(TEXTBEFORE(F2,"-",-1),"")

 

[Fluff]

Or even

=TEXTBEFORE(F2,"-",-1,,,"")

 

[ATHGR]

=IFERROR(TEXTBEFORE(F2,"-",-1),"")

Thanks a lot!!!

 

[Fluff]

Glad we could help & thanks for the feedback.