Ask Your Question

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

asked 2014-03-07 15:57:22 +0200

Bryan Neff gravatar image

updated 2015-11-03 21:20:40 +0200

Alex Kemp gravatar image

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 flag offensive 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

2 Answers

Sort by » oldest newest most voted

answered 2014-03-07 20:41:55 +0200

BasicMacro gravatar image

updated 2014-03-07 20:43:37 +0200

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 )
edit flag offensive delete link 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!

Bryan Neff gravatar imageBryan Neff ( 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., "\\[^\\]*$".

oweng gravatar imageoweng ( 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.

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

answered 2014-03-07 19:55:03 +0200

w_whalley gravatar image

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.

edit flag offensive delete link more


That works perfectly! Thank you so much! You're a hundred kinds of amazing.

Bryan Neff gravatar imageBryan Neff ( 2014-03-07 20:22:19 +0200 )edit

Question Tools

1 follower


Asked: 2014-03-07 15:57:22 +0200

Seen: 3,184 times

Last updated: Mar 07 '14