I’m getting data that I want to pick words out of but the word size is variable.
Example: /12345/abcdefghijk/BOS/NCK/NAC/BAC
I want the abcdefghijk data behind BOS but the abcdefghijk is variable in length.
I could break out the columns by “/” but those might be variable too.
Could try the “Text to Columns” command, and use the “/” as a delimiter.
Please explain what you mean by “pick words out of”. Do you simply want to know whether “abcdefghijk” is in there or not? Or do you need to manipulate it in some way?
I think he’s saying that what he wants might not always be after the second “/”. Another vote for we need more info.
So, what can be counted on to always surround the word?
Using the example from the OP and until we get more info, one way to do it would be to use LEFT, RIGHT, FIND, and LEN. I’ve broken the formula out into discrete bits to make it easier to see the relationships, but it could all be done as one formula:
A1: /12345/abcdefghijk/BOS/NCK/NAC/BAC
A3: =FIND("/BOS",A1)
A4: =LEFT(A1,A3-1)
A5: =FIND("/",A4,2)
A6: =RIGHT(A4,LEN(A4)-A5)
This sequence assumes (1) “/BOS” always immediately follows the target string (2) there are always 2 “/” characters in the string before the target string and the first one is the first character of the original string.
OK, I put multiple steps together to get the word to the left of “BOS” in the original. I put the original text in cell A1. Then I did the following:
in B1: FIND(“BOS”,A1) !find the position of BOS
in B2: LEFT(A1,B1-2) !truncate the right part of the string, from /BOS onward
in B3: LEN(B2)-LEN(SUBSTITUTE(B2,"/","")) !count the number of “/” remaining
in B4: FIND("/",B2,FIND("/",B2)+B3-1) !find the position of the last “/”
in B5: RIGHT(B2,LEN(B2)-B4) !truncate the left side of the string to keep only what you want
Yes there should be an = in all of the cells in column B. I was trying to put it all in one equation, but it gets really long.
I hope this is what the OP meant.
This is a nice trick.
Actually this step only works in the case in the OP. It won’t work if there are a few “/“s in front of the word desired.
in B4: FIND(”/”,B2,FIND("/",B2)+B3-1) !find the position of the last “/”
I was mislead by an entry I found through google. Curses.
Does excel not support regex?
I found that on a successful google search.
Another (hopefully) successful google search led to this as a substitute for cell B4
Cell B4: FIND(CHAR(1),SUBSTITUTE(B2,"/",CHAR(1),B3)) !finds last occurrence of “/” by substituting a dummy character for it in the string, then finding its location
yes, I could convert this and segregate the word I’m looking for but it might not end up in the same column because this is a data dump will all kinds of variation. The word behind BOS will always be between “/” symbols but it will vary in length and may contain numbers and text.
I’m looking through the other answers now but will not be able to try them for a day or two. I really appreciate the effort everybody is giving and don’t want anybody to think I’m not responding in a timely manner.
<lana>Nooooope.</lana>
“Well, that’s stupid.”
<lana>Yuuup!</lana>
To be more specific you can buy third party tools like SeoTools which will emulate regex functions in Excel, but it is kind of stupid considering that you can use regex as a built in function in the OpenOffice suite, Perl, Python, Lisp, Matlab, and for fuck’s sake even Microsoft Word. Why Microsoft wouldn’t institute at least a basic regex function across its so-called “suit” of semi-interoperable office productivity tools is a question that defies imagination. Even if most users wouldn’t use regex, for even the small percentage who are familiar with basic regular expressions it would provide very powerful functionality.
Stranger
Based on these assumptions:
[ol]
[li]/BOS exists in every string to be tested[/li][li]/BOS immediately follows the data you want returned[/li][li]The delimiter is ‘/’[/li][li]There is at least one delimiter before the data you’re looking for.[/li][li]There can be any number of delimiters in the string.[/li][li]The test string is in cell A1.[/li][li]The returned data is 99 characters or less.[/li][/ol]
Not pretty, but it will always return the string preceding ‘/BOS’
=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("/BOS",A1)-1),"/",REPT(" ",99)),99))
I suppose I could spoiler the explanation for those who want to figure it out for themselves, but you can just stop reading here if that’s the case. You know who you are.
Working from the inside out the way Excel will process it.
LEFT(A1,FIND("/BOS",A1)-1) strips off /BOS and everything after it.
SUBSTITUTE(…,"/",REPT(" ",99)) replaces all remaining instances of / with 99 spaces. This pads the data out to a minimum known length.
TRIM(RIGHT(…,99)) breaks off the rightmost 99 characters including the data you’re after and then removes whatever pad spaces that remained from the previous step.
I’ve tried all the different suggestions and they work. Thanks so much to everyone. I’ve never seen the substitute function before and don’t understand it but I’m going to study all the various formulas and put them in my “examples” sheet for future use.
I’m in my 50’s so I’ve seen the computer age evolve in my lifetime. I will never take the power of the internet for granted. The ability to ask a question and have people from around the world answer it is … I don’t have a word for it. Awesome, special, empowering… We’ve blown past a lot of the technology I thought was impossible when watching the original Star Trek as a kid. I can now ask my phone for various types of businesses and it will give me choices and navigate to them while playing any music I desire to hear.
And now thanks to all of you I don’t have to stare at mindless data for a few bits of fluff that would suck the life out of a busy day.