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)

01FI000389	876789	01.01.2012-01.07.2012		FI (fInance department)	Finance_Department_01.01- 01.07.2012_PN_876789
01FI000390	A23123	01.05.2016-01.11.2016		FI (fInance department)	Finance_Department_01.05- 01.11.2016_PN_A23123
01FI000391	767890	09.01.2018-09.07.2018		FI (fInance department)
02QC000340	878788	09.01.2018-09.07.2019		QC (Quality control)
02QC000341	345434	09.01.2018-09.07.2020		QC (Quality control)
02QC000342	876789	09.01.2018-09.07.2021		QC (Quality control)
03MI000425	989870	09.01.2018-09.07.2022		MI (Maintenance department)
03MI000426	678768	09.01.2018-09.07.2023		MI (Maintenance department)
03MI000427	565456	09.01.2018-09.07.2024		MI (Maintenance department)
10SR000001	989899	09.01.2018-09.07.2025		SR (Senior Management)
10SR000002	898989	09.01.2018-09.07.2026		SR (Senior Management)
10SR000003	787878	09.01.2018-09.07.2027		SR (Senior Management)
11BD000333	888989	09.01.2018-09.07.2028		BD (Board of Director)
11BD000334	998989	09.01.2018-09.07.2029		BD (Board of Director)
11BD000335	656567	09.01.2018-09.07.2030		BD (Board of Director)

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

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