Creating a dynamic formula to exceed words from a file name

[bearcub]

I have the following formula that I use to extract either "Accruals" or "Payments" from the current file name

MID(CELL("filename",'Comm_Sept 22 US Summary'!$A$1),
FIND("[",CELL("filename",'Comm_Sept 22 US Summary'!$A$1))+60,
FIND("]",CELL("filename",'Comm_Sept 22 US Summary'!$A$1))-
FIND("[",CELL("filename",'Comm_Sept 22 US Summary'!$A$1))-65)

The following month I will change the file name & the text length might change due to the current month (e.g. March - April, April - May, May - June , August - September, September - October, etc).

In other words, the file size will either expand or contract due to the processing month.

The current file name I am using is called "Compensation Summary US Oct 22 - September 22 Commission Payments".

Next month, I will change to the name to "Compensation Summary US Nov 22 - October 22 Commission Payments".

September has 9 letters and October contains 8.

When I process November for October (commissions are always processed the following month), I will have to change the 60 to 61 and 65 to 64 in the formula to be able to extract "Payments" from the File Name since October has 1 less letter than September

Is there a way of making the formula I am using dynamic so I won't have to manually update the formula each month? I am working that I might just have to settle for 3 letter month as opposed to using the full month name.

Thank you for your help in advance.

 

[gelu]

Hi Bearcub,

Here are two approaches to handle that:

Compensation Summary US Oct 22 - September 22 Commission Payments.xlsx
	A	B	C	D
1		Max characters in target strings	8
2		Payments	8
3		Accruals	8
4		Luckily 8 characters all the way
5		C:\MrExcel\[Compensation Summary US Oct 22 - September 22 Commission Payments.xlsx]Sheet1	Payments
6		C:\MrExcel\[Compensation Summary US Oct 22 - June 22 Commission Accruals.xlsx]Sheet1	Accruals
7
8		A different approach	String's length
9		Payments	8
10		Accruals	8
11		Overpayments	12
12		Biaccruals	10
13
14			Target String's start	What is?
15		C:\MrExcel\[Compensation Summary US Oct 22 - September 22 Commission Payments.xlsx]Sheet1	70	Payments
16		C:\MrExcel\[Compensation Summary US Oct 22 - September 22 Commission Accruals.xlsx]Sheet2	70	Accruals
17		C:\MrExcel\[Compensation Summary US Oct 22 - June 22 Commission Overpayments.xlsx]Sheet1	65	Overpayments
18		C:\MrExcel\[Compensation Summary US Oct 22 - June 22 Commission Biaccruals.xlsx]Sheet2	65	Biaccruals
Sheet1

Cell Formulas
Range	Formula
C1	C1	=MAX(C2:C3)
C9:C12,C2:C3	C2	=LEN(B2)
C5:C6	C5	=MID($B5,FIND(".",$B5)-$C$1,$C$1)
C15:C18	C15	=FIND(B9,B15)
D15:D18	D15	=MID(B15,C15,C9)

Cheers!

G

 

[gelu]

... and the second approach using a lookup table to select the right length whatever string appears in the name of the file.

Compensation Summary US Oct 22 - September 22 Commission Payments.xlsx
	A	B	C	D
1		Max characters in target strings	8
2		Payments	8
3		Accruals	8
4		Luckily 8 characters all the way
5		C:\MrExcel\[Compensation Summary US Oct 22 - September 22 Commission Payments.xlsx]Sheet1	Payments
6		C:\MrExcel\[Compensation Summary US Oct 22 - June 22 Commission Accruals.xlsx]Sheet1	Accruals
7
8		A different approach with a lookup table to adapt for changes)	String's length	Target String's start
9		Payments	8	70
10		Accruals	8	70
11		Overpayments	12	65
12		Biaccruals	10	65
13
14			Len
15		C:\MrExcel\[Compensation Summary US Oct 22 - September 22 Commission Payments.xlsx]Sheet1	89
16		C:\MrExcel\[Compensation Summary US Oct 22 - September 22 Commission Accruals.xlsx]Sheet2	89
17		C:\MrExcel\[Compensation Summary US Oct 22 - June 22 Commission Overpayments.xlsx]Sheet1	88
18		C:\MrExcel\[Compensation Summary US Oct 22 - June 22 Commission Biaccruals.xlsx]Sheet2	86
19
20				What is?
21		C:\MrExcel\[Compensation Summary US Oct 22 - June 22 Commission Overpayments.xlsx]Sheet1	-
22			-
23			65	1.353846154
24			-
25
26			Overpayments	12
27
Sheet1 (3)

Cell Formulas
Range	Formula
C1	C1	=MAX(C2:C3)
C15:C18,C9:C12,C2:C3	C2	=LEN(B2)
C5:C6	C5	=MID($B5,FIND(".",$B5)-$C$1,$C$1)
D9:D12	D9	=FIND(B9,B15)
B21	B21	=B17
C21:C24	C21	=IFERROR(FIND(B9,$B$21),"-")
D21:D24	D21	=IFERROR((C15/C21),"")
C26	C26	=MID(B21,MAX(C21:C24),INDEX(C9:C12,MATCH(MAX($D$21:$D$24),$D$21:$D$24,0),1))
D26	D26	=INDEX(C9:C12,MATCH(MAX(D21:D24),D21:D24,0),1)

 

[bearcub]

Thank for your the process and the formulas.

I will use this approach going forward.

Thank you for spending the time to provide me with this solution because it looks like this took a lot of effort on your side to make arrive at this solution.