Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

excel 2007 duplicate data with unc path

Posted on 2010-11-22
10
Medium Priority
?
374 Views
Last Modified: 2012-06-27
Hi

I have 2 columns in excel 2007 with thousands of entries - ColumnA and ColumnB like so:

ColumnA                                       ColumnB
\\unc\path1\path2\word.doc           Word.doc

What I would like to do and am struggling to find an answer is take ColumnB as an input and remove the duplicated word.doc from columnA, so I would be left with the following:

ColumnA                                       ColumnB
\\unc\path1\path2\                         Word.doc

I am unable to use find replace with wildcards using \ as an anchor, as I have multiple in the unc paths..

Any ideas?

regards
Bryan


0
Comment
Question by:bryan oakley-wiggins
  • 6
  • 4
10 Comments
 
LVL 13

Accepted Solution

by:
gbanik earned 2000 total points
ID: 34188750
Place this formula in the Column C ... if the first 2 columns are filled with the above values
=IF(MID(A1,LEN(A1)-LEN(B1)+1,LEN(A1))=B1,MID(A1,1,LEN(A1)-LEN(B1)),A1)
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34188767
Why substitute should not be used directly is because there could be the text "Word.doc" within the UNC path and just not at the end.
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34188824
In order to eliminate everthing after that last "\"... use this formula
=MID(A1,1,FIND("|",SUBSTITUTE(A1,"\","|",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 13

Expert Comment

by:gbanik
ID: 34188859
Use this formula
=MID(A1,1,FIND("|",SUBSTITUTE(A1,"\","|",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))
in conjunction to the text comparison in the first formula to give best results (ie eliminate the last part only if the last part of the text matches the file name).
=IF(MID(A1,LEN(A1)-LEN(B1)+1,LEN(A1))=B1,MID(A1,1,FIND("|",SUBSTITUTE(A1,"\","|",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))),A1)
0
 

Author Comment

by:bryan oakley-wiggins
ID: 34189180
hi gbanik

thanks for the reply, much appreciated.
do I highlight the whole column and add the formula?

will this then give me the following?

ColumnA                               ColumnB
\\unc\path1\path2\                 Word.doc
\\unc\path1\path2\                 Word2.doc
\\unc\path1\path2\                 other.html

etc


0
 

Author Comment

by:bryan oakley-wiggins
ID: 34189393
also the ColumnA unc paths are different lengths
0
 

Author Comment

by:bryan oakley-wiggins
ID: 34189470
hi

ok, I have it working on 1 cell - I have about 21000 rows - how do I apply to all in the columns??
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34189776
Couple of ways..
1. Just double click the right hand corner of the C1 (where the formula is)
2. Copy cell C1... select the complete range from C2 to the last cell (say C21000 if 21000 is the last row) .. and paste
0
 
LVL 13

Expert Comment

by:gbanik
ID: 34189783
.....le click the right hand "bottom" corner ....
0
 

Author Closing Comment

by:bryan oakley-wiggins
ID: 34189788
brilliant - worked for me - I just dragged throughout the column and hey presto..!

Marvellous, thanks so much for your time.

regards
Bryan
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

927 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question