Need help with formula. - perhaps wildcard and a mix of indirect?

[JDJong]

Hi,

need to automize the description based 3 criteria.
- Personnel number (#3 and #4 are letters short for department)
- Personnel code (random)
- Appointed period

would like to have the description within such format: e.g:Finance_Department_01.01- 01.07.2012_PN_876789 (Indicated in red)

[TABLE="width: 1025"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]01FI000389[/TD]
[TD="align: right"]876789[/TD]
[TD]01.01.2012-01.07.2012[/TD]
[TD][/TD]
[TD]FI (fInance department)[/TD]
[TD]Finance_Department_01.01- 01.07.2012_PN_876789[/TD]
[/TR]
[TR]
[TD]01FI000390[/TD]
[TD]A23123[/TD]
[TD]01.05.2016-01.11.2016[/TD]
[TD][/TD]
[TD]FI (fInance department)[/TD]
[TD]Finance_Department_01.05- 01.11.2016_PN_A23123[/TD]
[/TR]
[TR]
[TD]01FI000391[/TD]
[TD="align: right"]767890[/TD]
[TD]09.01.2018-09.07.2018[/TD]
[TD][/TD]
[TD]FI (fInance department)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02QC000340[/TD]
[TD="align: right"]878788[/TD]
[TD]09.01.2018-09.07.2019[/TD]
[TD][/TD]
[TD]QC (Quality control)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02QC000341[/TD]
[TD="align: right"]345434[/TD]
[TD]09.01.2018-09.07.2020[/TD]
[TD][/TD]
[TD]QC (Quality control)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02QC000342[/TD]
[TD="align: right"]876789[/TD]
[TD]09.01.2018-09.07.2021[/TD]
[TD][/TD]
[TD]QC (Quality control)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03MI000425[/TD]
[TD="align: right"]989870[/TD]
[TD]09.01.2018-09.07.2022[/TD]
[TD][/TD]
[TD="colspan: 2"]MI (Maintenance department)[/TD]
[/TR]
[TR]
[TD]03MI000426[/TD]
[TD="align: right"]678768[/TD]
[TD]09.01.2018-09.07.2023[/TD]
[TD][/TD]
[TD="colspan: 2"]MI (Maintenance department)[/TD]
[/TR]
[TR]
[TD]03MI000427[/TD]
[TD="align: right"]565456[/TD]
[TD]09.01.2018-09.07.2024[/TD]
[TD][/TD]
[TD="colspan: 2"]MI (Maintenance department)[/TD]
[/TR]
[TR]
[TD]10SR000001[/TD]
[TD="align: right"]989899[/TD]
[TD]09.01.2018-09.07.2025[/TD]
[TD][/TD]
[TD="colspan: 2"]SR (Senior Management)[/TD]
[/TR]
[TR]
[TD]10SR000002[/TD]
[TD="align: right"]898989[/TD]
[TD]09.01.2018-09.07.2026[/TD]
[TD][/TD]
[TD="colspan: 2"]SR (Senior Management)[/TD]
[/TR]
[TR]
[TD]10SR000003[/TD]
[TD="align: right"]787878[/TD]
[TD]09.01.2018-09.07.2027[/TD]
[TD][/TD]
[TD="colspan: 2"]SR (Senior Management)[/TD]
[/TR]
[TR]
[TD]11BD000333[/TD]
[TD="align: right"]888989 [/TD]
[TD]09.01.2018-09.07.2028[/TD]
[TD][/TD]
[TD]BD (Board of Director)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11BD000334[/TD]
[TD="align: right"]998989[/TD]
[TD]09.01.2018-09.07.2029[/TD]
[TD][/TD]
[TD]BD (Board of Director)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11BD000335[/TD]
[TD="align: right"]656567[/TD]
[TD]09.01.2018-09.07.2030[/TD]
[TD][/TD]
[TD]BD (Board of Director)[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Please provide inputs. Thanks

 

[steve the fish]

Why is the year missing from the first date? That doesnt make sense really as many entries cover more than one year.

 

[JDJong]

Why is the year missing from the first date? That doesnt make sense really as many entries cover more than one year.

If it is within the same year, it is not necessary to have the first date entered.

 

[steve the fish]

If it is within the same year, it is not necessary to have the first date entered.

That would make it a much longer formula than this:

=PROPER(SUBSTITUTE(SUBSTITUTE(MID(E1,FIND("(",E1)+1,999),")","")," ","_"))&"_"&C1&"_PN_"&B1

 

[JDJong]

That would make it a much longer formula than this:

=PROPER(SUBSTITUTE(SUBSTITUTE(MID(E1,FIND("(",E1)+1,999),")","")," ","_"))&"_"&C1&"_PN_"&B1

Looks quite advanced ... apologies, the Appointed period (C) are splited into 2 columns : Appointed since and Appointed until.

how should I alter the formula?

Thanks

 

[steve the fish]

The bit where it says

&C1&

change to

&C1&"-"&D1&

 

[JDJong]

The bit where it says

&C1&

change to

&C1&"-"&D1&

I have entered the following formula:

=PROPER(SUBSTITUTE(SUBSTITUTE(MID(AF4;FIND("(";AF4)+1,999);")";"");" ";"_"))&"_"&Z4 - AA4&"_PN_"&A4

It says i have entered too many arguements?

anything i add in extra?

 

[steve the fish]

Try this:

=PROPER(SUBSTITUTE(SUBSTITUTE(MID(AF4;FIND("(";AF4)+1;999);")";"");" ";"_"))&"_"&Z4&"-"&AA4&"_PN_"&A4

 

[JDJong]

The bit where it says

&C1&

change to

&C1&"-"&D1&

and within the master table, there is no E column. I manually input just for better clarification.

perhaps to add the "finance department" "quality control" within the formula?

Thanks

 

[JDJong]

Try this:

=PROPER(SUBSTITUTE(SUBSTITUTE(MID(AF4;FIND("(";AF4)+1;999);")";"");" ";"_"))&"_"&Z4&"-"&AA4&"_PN_"&A4

I don't seem to get it to work. It either returned #value or 0.

any idea what went wrong? or perhaps a different formula without proper would do?

Thanks