Copying part of a string after instance of a character [closed]

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?

edit retag reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2015-11-03 21:20:51.244071

Sort by » oldest newest most voted

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 )

more

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!

( 2014-03-07 21:16:24 +0200 )edit

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., "\$^\$*\$".

( 2014-03-08 00:43:57 +0200 )edit

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.

( 2015-08-18 14:41:58 +0200 )edit

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.

more