MID Error extracting from a PDF

[rex759]

Hello,
My goal is to show the text between the numbers. I think the problem I am having is with the Substitute function. I am finding I have to update it based on the number of spaces in the string. Substitute(text,old_text,new_text,[instance_num]

I started out with an Instance_num of 13 which worked 90% of the time. Then I found I had to change it from 13 to 12 or 11 to get some of the other examples to work.

I am confused as to why it doesn't work for all the entries.

Column B shows the results I want.

2022 Find characters in a string.xlsx
	A	B
1	0000-111-222 Cat-00FT Sss/Bbb KD-HT PRIME SPF 1.11 1111 1111 111 0000-111-111 _______ 1111 1111 _______	Cat-00FT Sss/Bbb KD-HT PRIME SPF
2	0000-222-333 DOGSS DOVER PRIVACY PANEL 222.20 2 22 12 2222-333-444 _______ 9994 0 _______	DOGSS DOVER PRIVACY PANEL
3	0000-444-555 cow xXx Ooo 44.44 777 777 777- 0000-999-999 _______ 3000 33 _______	cow xXx Ooo
Sheet1

Cell Formulas
Range	Formula
B1	B1	=MID(A1,FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-13),1),FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-8),1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-13),1))
B2	B2	=MID(A2,FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-12),1),FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-8),1)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-12),1))
B3	B3	=MID(A3,FIND("*",SUBSTITUTE(A3," ","*",LEN(A3)-LEN(SUBSTITUTE(A3," ",""))-11),1),FIND("~",SUBSTITUTE(A3," ","~",LEN(A3)-LEN(SUBSTITUTE(A3," ",""))-8),1)-FIND("*",SUBSTITUTE(A3," ","*",LEN(A3)-LEN(SUBSTITUTE(A3," ",""))-11),1))

 

[jtakw]

Hi,

Try this, based on your 3 samples:

Book3.xlsx
	A	B
1	0000-111-222 Cat-00FT Sss/Bbb KD-HT PRIME SPF 1.11 1111 1111 111 0000-111-111 _______ 1111 1111 _______	Cat-00FT Sss/Bbb KD-HT PRIME SPF
2	0000-222-333 DOGSS DOVER PRIVACY PANEL 222.20 2 22 12 2222-333-444 _______ 9994 0 _______	DOGSS DOVER PRIVACY PANEL
3	0000-444-555 cow xXx Ooo 44.44 777 777 777- 0000-999-999 _______ 3000 33 _______	cow xXx Ooo
Sheet1070

Cell Formulas
Range	Formula
B1:B3	B1	=TRIM(MID(SUBSTITUTE(LEFT(A1,FIND(".",A1))," ",REPT(" ",100),LEN(LEFT(A1,FIND(".",A1)))-LEN(SUBSTITUTE(LEFT(A1,FIND(".",A1))," ",""))),FIND(" ",A1)+1,100))

 

[rex759]

Hello, thank you for responding so quickly. Your formula worked for most of the data however, some of the entries have a period in the description.
For example,

0000-121-586 .090 FRP WALL PANEL 4X8 WHITE 29.98 127 244 117 0000-121-586 _______ 3137 223 _______

and it only returned a decimal point.

 

[jtakw]

Well, I did say "based on your 3 samples", it's always a good idea to show samples for all possible text strings for text extraction questions.

What other possible variations might there be?

 

[rex759]

1006-828-603 WA51A5505AC-5.1 CU FT TL WASHER 1049.00 0 2 2 1006-828-603 _______ 4997 1 _______
this should be starting with WA to washer

1005-929-302 3/4 HP. C WI-FI ENABLED ULTRA-QUIET 198.00 3 12 9 1005-929-302 _______ 1780 8 _______
this should be 3/4 HP to Quiet

1002-961-483 4"X75' SUPER STICK WINDOW SEALING T 28.98 60 0 60- 1002-961-483 _______ *N/A 0 _______
This should be 4"x75' to Sealing T

but the majority of your formula worked

 

[jtakw]

Including your 4 new samples, this should do it, it's starting to look not very pretty:

Book3.xlsx
	A	B
1	0000-111-222 Cat-00FT Sss/Bbb KD-HT PRIME SPF 1.11 1111 1111 111 0000-111-111 _______ 1111 1111 _______	Cat-00FT Sss/Bbb KD-HT PRIME SPF
2	0000-222-333 DOGSS DOVER PRIVACY PANEL 222.20 2 22 12 2222-333-444 _______ 9994 0 _______	DOGSS DOVER PRIVACY PANEL
3	0000-444-555 cow xXx Ooo 44.44 777 777 777- 0000-999-999 _______ 3000 33 _______	cow xXx Ooo
4	0000-121-586 .090 FRP WALL PANEL 4X8 WHITE 29.98 127 244 117 0000-121-586 _______ 3137 223 _______	.090 FRP WALL PANEL 4X8 WHITE
5	1006-828-603 WA51A5505AC-5.1 CU FT TL WASHER 1049.00 0 2 2 1006-828-603 _______ 4997 1 _______	WA51A5505AC-5.1 CU FT TL WASHER
6	1005-929-302 3/4 HP. C WI-FI ENABLED ULTRA-QUIET 198.00 3 12 9 1005-929-302 _______ 1780 8 _______	3/4 HP. C WI-FI ENABLED ULTRA-QUIET
7	1002-961-483 4"X75' SUPER STICK WINDOW SEALING T 28.98 60 0 60- 1002-961-483 _______ *N/A 0 _______	4"X75' SUPER STICK WINDOW SEALING T
Sheet1070

Cell Formulas
Range	Formula
B1:B7	B1	=TRIM(MID(SUBSTITUTE(LEFT(A1,FIND(".",A1,IF(LEN(A1)-LEN(SUBSTITUTE(A1,".",""))=1,1,FIND(".",A1)+1)))," ",REPT(" ",100),LEN(LEFT(A1,FIND(".",A1,IF(LEN(A1)-LEN(SUBSTITUTE(A1,".",""))=1,1,FIND(".",A1)+1))))-LEN(SUBSTITUTE(LEFT(A1,FIND(".",A1,IF(LEN(A1)-LEN(SUBSTITUTE(A1,".",""))=1,1,FIND(".",A1)+1)))," ",""))),FIND(" ",A1)+1,100))

 

[rex759]

Yours is much better than my solution. Thank you. Mine was much larger,
=IF(F2="","",IFERROR(MID(B2,FIND("*",SUBSTITUTE(B2," ","*",LEN(B2)-LEN(SUBSTITUTE(B2," ",""))-13),1),FIND("~",SUBSTITUTE(B2," ","~",LEN(B2)-LEN(SUBSTITUTE(B2," ",""))-8),1)-FIND("*",SUBSTITUTE(B2," ","*",LEN(B2)-LEN(SUBSTITUTE(B2," ",""))-13),1)),IFERROR(MID(B2,FIND("*",SUBSTITUTE(B2," ","*",LEN(B2)-LEN(SUBSTITUTE(B2," ",""))-12),1),FIND("~",SUBSTITUTE(B2," ","~",LEN(B2)-LEN(SUBSTITUTE(B2," ",""))-8),1)-FIND("*",SUBSTITUTE(B2," ","*",LEN(B2)-LEN(SUBSTITUTE(B2," ",""))-12),1)),MID(B2,FIND("*",SUBSTITUTE(B2," ","*",LEN(B2)-LEN(SUBSTITUTE(B2," ",""))-11),1),FIND("~",SUBSTITUTE(B2," ","~",LEN(B2)-LEN(SUBSTITUTE(B2," ",""))-8),1)-FIND("*",SUBSTITUTE(B2," ","*",LEN(B2)-LEN(SUBSTITUTE(B2," ",""))-11),1)))))

 

[jtakw]

You're welcome, thanks for the feedback.

Your formula above doesn't work for A4, A6, and A7 in my table in Post # 6, and all results has a leading Space.