Mid function breakdown

[egut]

Can someone please breakdown in layman's terms the num_chars part of this function for my formula: =MID(C40,(SEARCH("""",C40,1)+1),(SEARCH("""",C40,((SEARCH("""",C40,1))+1)))-(SEARCH("""",C40,1))-1)

When I separate SEARCH("""",B48,SEARCH("""",B48,1)+1)-(SEARCH("""",B48,1)-1) into its own cell for portfolio="qen002524" I get 11 but I can't explain it. The whole function is "simply" returning whatever is in b/t the quotes but the last part is confusing me when I try to dissect it.

Does the num_chars part say I am starting at the second quote less anything before the character after the first quote? The parenthesis make it really confusing and could be place in different spots but I am having trouble. Let me know if this makes sense.

Thanks

[Tony Valko]

For the num_chars you want to find the char_num-1 of the last " and subtract the char_num+1 of the 1st ".

If the string to be extracted is always at the end of the larger string then try this version:

=SUBSTITUTE(MID(C40,FIND("""",C40)+1,50),"""","")

[Ron Coderre]

That formula uses a round-about way of calculating the start and length arguments of the MID function.

Here are some other options:
If the quoted value can be anywhere in cell C40
=MID(LEFT(C40,FIND("|",SUBSTITUTE(C40,"""","|",2))-1),FIND("""",C40)+1,255)

If the quoted value is ALWAYS at the end of the string in cell C40
=MID(LEFT(C40,LEN(C40)-1),FIND("""",C40)+1,LEN(C40))

Are either of those easier to decipher?

[egut]

Ron, what does the pipe mean or signify? Not sure where that falls into place

Also, is the num_chars arbitrary b/c both of you have quite large numbers?

[Tony Valko]

is the num_chars arbitrary b/c both of you have quite large numbers?

Basically, yes.

num_chars needs to be a big enough number that it returns all the chars after the 1st quote.

I assumed that your example string:

portfolio="qen002524"

Was typical of your data so num_chars of 50 would suffice. You can always use a smaller number if you want since no one knows your data like you do!

[egut]

correct, almost all of the data is similar in that it will look like XXXXXXX="*******", but i was just wondering. Thanks

[Tony Valko]

Thanks

You're welcome!

[Ron Coderre]

Ron, what does the pipe mean or signify? Not sure where that falls into place

In the formula I posted: =MID(LEFT(C40,FIND("|",SUBSTITUTE(C40,"""","|",2))-1),FIND("""",C40)+1,255)
the SUBSTITUTE function replaces the second quote (") with a pipe character (|).
It breaks down this way:
C40: portfolio="some value"

=MID(LEFT(C40,FIND("|",SUBSTITUTE(C40,"""","|",2))-1),FIND("""",C40)+1,255)
=MID(LEFT(C40,FIND("|","portfolio=""some value|")-1),FIND("""",C40)+1,255)
=MID(LEFT(C40,21),FIND("""",C40)+1,255)
=MID("portfolio=""some value",FIND("""",C40)+1,255)
=MID("portfolio=""some value",12,255)
="some value"