How to split using the last backslash character as delimiter in excel 2013?

Question

We have a list with 1000's of values in a single column.The values will be in this format

W:\RT_QAQC\Received\20160411_GDM_QA\VD\RegRef\Afr\Geology\IAE_Geology_Africa_10M
W:\RT_QAQC\Received\20160411_GDM_QA\VD\GlobRef\Ind\GIS\SS_GIS_Ind

I would like to split it using the last backslash a delemiter. So the result will be

W:\RT_QAQC\Received\20160411_GDM_QA\VD\RegRef\Afr\Geology
W:\RT_QAQC\Received\20160411_GDM_QA\VD\GlobRef\Ind\GIS

And in the next column,

IAE_Geology_Africa_10M
SS_GIS_Ind

I've tried with this one

=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",LEN(A1))),LEN(A1)))

But it's only copying the characters after the last backslash

score 2 · Accepted Answer · edited May 23 '17 at 12:16

2

How about:

=MID(A1,1,FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))-1)

This uses a simple technique to determine the position of the last occurrence of a substring in a string.

Another approach is to use tigeravatar's approach

edited May 23 '17 at 12:16

Community

1
1

answered May 05 '16 at 11:37

Gary's Student

95,722
10
59
99

I was just about to explore the MID approach. Thought it would be cleaner – Forward Ed May 05 '16 at 11:39
Excellent. Thanks a lot. – joseph_k May 05 '16 at 12:38
Very clever! Thank you! – Jamie Hanrahan Jan 12 '18 at 00:33

Forward Ed · Answer 2 · 2016-05-05T11:43:45.497

0

Assuming your source data is in column A and you do not have a header row and your data start in row 1. In C1 use the formula you posted as it seems to do what you want for the second column. It also makes our life easIer on how to deal with the first column. In B1 place the following.

=LEFT(A1,LEN(A1)-LEN(C1)-1)

Results

edited May 05 '16 at 11:43

answered May 05 '16 at 10:58

Forward Ed

9,484
3
22
52

LEN(C1)+1. Thats the problem. the length of the characters are not unifrom – joseph_k May 05 '16 at 11:24
Change the +1 to a -1 and you are off to the races – Forward Ed May 05 '16 at 11:41

How to split using the last backslash character as delimiter in excel 2013?

2 Answers2