Searching through strings with formulas

[will.smith3]

I am working on getting good at searching through strings using LEFT, RIGHT, MID, FIND, and SEARCH. But, need more practice at this skill. Here's the issue I am facing...

Given the below string, which is broken up into three parts: 1) A name, which may/may not contain a space, 2) a value in B, KB, or MB, and, 3) number of items in that folder.

"Sent Items 452.5 MB (474,439,198 bytes) 4549"

I have thought about this a few ways and the easiest one that comes to mind is to search from right-left and find the ")" then keep going left to the "B" - since each one will have that. From there I would search until the second space, and return all of that. That's my pseudocode, but I don't know how to write this out into a working formula.

Below is the code I have so far to try and get everything in green.

=RIGHT(A21,LEN(A21)-FIND(")",SUBSTITUTE(A21," ",")")))

Which yields...

"Items 452.5 MB (474,439,198 bytes) 4549"


All help is greatly appreciated.

 

[Deepak]

Try this..

=MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),FIND(")",A2)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1)

 

[will.smith3]

That worked! Thank you. Would you mind breaking this down for me and explaining each part as I want to learn and not just copy other people's work. How did you work through this in your own mind?

 

[Deepak]

Ya!

First thing which came to light to find first integer in the string.

So, converted search string to A2&"0123456789" as this will be error free in case any numeric doesn't exist in A2.

Now, The task is here to fnd numeric where same might be repeated due to additional numeric addition "0123456789" with the string.

Thus used min function to find the very 1st place where numeric encounters.

Find represents = {40,25,9,22,7,8,46,18,27,23}
& using min with it produced 7 which is 1st numeric place in the string i.e 4.

So, Now we have the staring point for MID.

Second part is to find how much string after it needs to extracted till ")".

So used (LEN(string)-7)+1.


Hope! i clarified it.