Modification to very useful text trim formula

Welcome to the forum Morph1ne.

Maybe

Formula:

Please Login or Register  to view this content.

Or this.

Formula:

Please Login or Register  to view this content.

Sorry I didn't read the "varying" part carefully. Put the varying items in B1:C1 and replace by reference.

Formula:

Please Login or Register  to view this content.

Try the formula in post #3. Since you want only the numbers that formula extracts numbers only. There is no need for substitutions.

	A	B	C
1	aaaaX123Yaaaa	123	In B1: =SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$30),1))*ROW($1:$30)+1,0),ROW($1:$30)),1)*10^ROW($1:$30)/10)

OK this is a much different problem.

With QL-A\PMH.1\PFWIC\PCL=94.07\PIL=90.43\PSF=0.33d\P{\C24;Assumed To Be Clay Pipes} what do you want returned? There are several numbers.

Here are two examples with "boundary" texts in columns B:C

	A	B	C	D	E
1	QL-A\PMH.1\PFWIC\PCL=94.07\PIL=90.43\PSF=0.33d\P{\C24;Assumed To Be Clay Pipes}	.	\	1	In D1: =--TRIM(LEFT(SUBSTITUTE(MID(A1,FIND(B1,A1)+1,99),C1,REPT(" ",99)),99))
2	QL-A\PMH.1\PFWIC\PCL=94.07\PIL=90.43\PSF=0.33d\P{\C24;Assumed To Be Clay Pipes}	=	\	94.07

We are suffering from unrepresentative data here. Please post an excel sheet with CLEAR before/after positions.


Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

... or going back to your original Q... does this work???

=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("X",A1)+1,LEN(A1)),"Y",REPT(" ",100)),100))

What Glenn says.

If you would post an Excel workbook (not pics or screenshots ... saves retyping data)

Include about 10-20 examples. Provide a mix of the strings you will be working with.

If you are not familiar with how to do this:

To attach a file to your post,

• be sure to desensitize the data
• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

Another shot in the dark..... Is this what you want?????


=TRIM(LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(1:10000)))))

assumes data in A1 and it returns 1 from:

QL-A\PMH.1\PFWIC\PCL=94.07\PIL=90.43\PSF=0.33d\P{\C24;Assumed To Be Clay Pipes}

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.