Formula To Return TRUE If Text In Cell Is Either xxxx.xxx or 11-Digits

[JuicyMusic]

Hi gurus, I know the formula I'm asking for is not great...but I can't come up with anything else to solve my problem.
FYI - We have hundreds of 11-digit general ledger codes and 7-digit sub-ledger codes. I'm providing 1 example for each.


Formula needed: If the target cell contains this type of text 2007.030 (4 digits--a period--3 digits).....Then return this text: "Sub-Ledger Code for 16360045"
If the target cell contains this type of text 60070055600 w/ exactly 11 digits (no periods).....Then return this number 60070055600.

If target cell doesn't contain either of the above.....Then return something like..."Pls check GL coding".

Suggestions are welcome.
Thank you so much!
Juicy,

 

[dreid1011]

Hi gurus, I know the formula I'm asking for is not great...but I can't come up with anything else to solve my problem.
FYI - We have hundreds of 11-digit general ledger codes and 7-digit sub-ledger codes. I'm providing 1 example for each.


Formula needed: If the target cell contains this type of text 2007.030 (4 digits--a period--3 digits).....Then return this text: "Sub-Ledger Code for 16360045"
If the target cell contains this type of text 60070055600 w/ exactly 11 digits (no periods).....Then return this number 60070055600.

If target cell doesn't contain either of the above.....Then return something like..."Pls check GL coding".

Suggestions are welcome.
Thank you so much!
Juicy,

Perhaps this, though the second criteria, is it to return that number specifically, or if the format matches 11 digits without a period, to return the number itself?

Book1
	A	B	C
1	2007.030		Sub-Ledger Code for 16360045
2	60070055600		60070055600
3	201.3458		Pls check GL coding
4	1234567891		Pls check GL coding
5			Pls check GL coding
Sheet1

Cell Formulas
Range	Formula
C1:C5	C1	=IF(AND(MID(A1,5,1)=".",LEN(A1)=8),"Sub-Ledger Code for 16360045",IF(AND(IFERROR(FIND(".",A1),1),LEN(A1)=11),"60070055600","Pls check GL coding"))

 

[ExceLoki]

could also try this
---------------------

Book1
	A	B
1	2007.030	Sub-Ledger Code for 16360045
2	60070055600	60070055600
3	1234.654	Sub-Ledger Code for 16360045
4	123.546	Pls check GL coding
5		Pls check GL coding
Sheet1

Cell Formulas
Range	Formula
B1:B5	B1	=IFERROR(IFS(AND(LEN(TEXTBEFORE(TEXT(A1,"###0.000"),".",))=4,LEN(TEXTAFTER(TEXT(A1,"###0.000"),".",))=3),"Sub-Ledger Code for 16360045",LEN(A1)=11,A1),"Pls check GL coding")

 

[JuicyMusic]

Perhaps this, though the second criteria, is it to return that number specifically, or if the format matches 11 digits without a period, to return the number itself?

Book1
	A	B	C
1	2007.030		Sub-Ledger Code for 16360045
2	60070055600		60070055600
3	201.3458		Pls check GL coding
4	1234567891		Pls check GL coding
5			Pls check GL coding
Sheet1

Cell Formulas
Range	Formula
C1:C5	C1	=IF(AND(MID(A1,5,1)=".",LEN(A1)=8),"Sub-Ledger Code for 16360045",IF(AND(IFERROR(FIND(".",A1),1),LEN(A1)=11),"60070055600","Pls check GL coding"))

Yes, you are correct. If the target cells contains an 11-digit number, then the wanted result is that it returns itself. TY!

 

[dreid1011]

Yes, you are correct. If the target cells contains an 11-digit number, then the wanted result is that it returns itself. TY!

Okay, then if you choose to use my solution, here is the proper version to return the number itself, otherwise Loki's solution looks perfectly suitable as well with that already taken into account.

=IF(AND(MID(A1,5,1)=".",LEN(A1)=8),"Sub-Ledger Code for 16360045",IF(AND(IFERROR(FIND(".",A1),1),LEN(A1)=11),A1,"Pls check GL coding"))

 

[JuicyMusic]

could also try this
---------------------

Book1
	A	B
1	2007.030	Sub-Ledger Code for 16360045
2	60070055600	60070055600
3	1234.654	Sub-Ledger Code for 16360045
4	123.546	Pls check GL coding
5		Pls check GL coding
Sheet1

Cell Formulas
Range	Formula
B1:B5	B1	=IFERROR(IFS(AND(LEN(TEXTBEFORE(TEXT(A1,"###0.000"),".",))=4,LEN(TEXTAFTER(TEXT(A1,"###0.000"),".",))=3),"Sub-Ledger Code for 16360045",LEN(A1)=11,A1),"Pls check GL coding")

Loki, I really like your suggestion since it accounts for typos's......but your formula's bring back "Pls Check GL Coding". Can you try again and see? Thanks!

 

[ExceLoki]

can you give me some examples where it's returning the wrong values?

currently it's returning:
• 4 digits"."3 digits= "Sub-Ledger Code for 16360045"
• 11 digits (no periods)= referenced number
• if neither= "Pls check GL coding" (this would include: blanks, 4+/- digits"."3 digits, not exactly 11 w/out ".")
as requested in the OP

however mine does not correctly return 4 digits"."3+/- digits, will get Sub-Ledger Code for 16360045
as shown below

multiple posts solutions-v3.xlsm
	A	B	C
1	input	result	format
2	60070055600	60070055600	11
3	12345678901	12345678901	11
4	1234567890	Pls check GL coding	10
5	123456789012	Pls check GL coding	12
6	2007.030	Sub-Ledger Code for 16360045	4.3
7	1234.123	Sub-Ledger Code for 16360045	4.3
8	123.123	Pls check GL coding	3.3
9	12345.123	Pls check GL coding	5.3
10		Pls check GL coding	blank
11	1234.12	Sub-Ledger Code for 16360045	4.2
12	1234.1234	Sub-Ledger Code for 16360045	4.4
13	1234.1	Sub-Ledger Code for 16360045	4.1
14	1234	Sub-Ledger Code for 16360045	4.0
ifs dec or 11 digit

Cell Formulas
Range	Formula
B2:B14	B2	=IFERROR(IFS(AND(LEN(TEXTBEFORE(TEXT(A2,"###0.000"),".",))=4,LEN(TEXTAFTER(TEXT(A2,"###0.000"),".",))=3),"Sub-Ledger Code for 16360045",LEN(A2)=11,A2),"Pls check GL coding")

 

[ExceLoki]

however mine does not correctly return 4 digits"."3+/- digits, will get Sub-Ledger Code for 16360045

i may have solved that though, except 4digits.2digits is still returning "Sub-Ledger Code for 16360045" as it's adding a 0 at the end
---------------------

multiple posts solutions-v3.xlsm
	A	B	C
1	input	new result	format
2	60070055600	60070055600	11
3	12345678901	12345678901	11
4	1234567890	Pls check GL coding	10
5	123456789012	Pls check GL coding	12
6	2007.030	Sub-Ledger Code for 16360045	4.3 end 0
7	1234.123	Sub-Ledger Code for 16360045	4.3
8	123.123	Pls check GL coding	3.3
9	12345.123	Pls check GL coding	5.3
10		Pls check GL coding	blank
11	1234.120	Sub-Ledger Code for 16360045	4.2
12	1234.1234	Pls check GL coding	4.4
13	1234.100	Pls check GL coding	4.1
14	1234.000	Pls check GL coding	4.0
ifs dec or 11 digit

Cell Formulas
Range	Formula
B2:B14	B2	=IFERROR(IFS(AND(LEN(TEXTBEFORE(TEXT(A2,"###0.000"),".",))=4,IF(OR(LEN(A2)<7,LEN(A2)>8),FALSE,LEN(TEXTAFTER(TEXT(A2,"###0.000"),".",))=3)),"Sub-Ledger Code for 16360045",LEN(A2)=11,A2),"Pls check GL coding")