Pretty simple. As an example:
.\Archive\(20140114) G2-253_G2-252 (Upstream)\(20140114) G2-253_G2-252 (Upstream).mpg
Needs to JUST become: (20140114) G2-253_G2-252 (Upstream).mpg in a different column
Problem : The amount of characters in a string can vary, and is not static.
What I want: I can put thousands of the first example in column A. I want column B to just show everything after the third ‘’. Is there a code that will put everything after the third ‘’ into column B?
If you always have three backslashes, Copy column a to column b. Select column b. On the menu choose Data/Text to columns. Set the separator option to Other and make it a backslash. Click OK. Discard the three unneeded columns.
That works perfectly! Thank you so much! You’re a hundred kinds of amazing.
A less elegant way using a formula
=RIGHT(A1, LEN(A1) - (SEARCH("",A1,SEARCH("",A1,SEARCH("",A1)+1)+1)) )
To find the location of the third backslash
- Basically it searches for the location of the first backslash
- Then it starts another search from that location to find the second backslash
- Then another from that location to find the location of the third backslash
Using the RIGHT function
- RIGHT( Text , Number of chars at the end of the string to keep )That’s good to know as well! Both solutions are very good, and I might use that as well just to take out the extra steps. Thanks! You both have saved me a lot of time. If it gives you an idea, what we had to do before this was copy/paste the appropriate information out of every line. Per project, there could literally be thousands of lines. Thanks!
A more elegant way using a formula: =RIGHT(A1;LEN(A1)-SEARCH("([\\][^\\]*)?$";A1)) where the string is in cell A1. This method does not require adjusting the offset. It searches for a backslash [\\] followed by any number of non-backslash characters [^\\]* that are at the line end $. I have made the search group optional (...)? but this could be omitted and simplified if required e.g., "\\[^\\]*$".
Thank you oweng, I’ve always been afraid of regular expressions, as I find them very complicated at times, but your explanation made it very easy indeed. This was more useful for me finding the last blank space, because I don’t always have the same number of spaces in the string.