Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# excel 2007 duplicate data with unc path

Posted on 2010-11-22
Medium Priority
371 Views
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
Question by:bryan oakley-wiggins
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 6
• 4

LVL 13

Accepted Solution

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

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

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

LVL 13

Expert Comment

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

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

ID: 34189393
also the ColumnA unc paths are different lengths
0

Author Comment

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

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

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

Author Closing Comment

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

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
###### Suggested Courses
Course of the Month8 days, 12 hours left to enroll