Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 375
  • Last Modified:

excel 2007 duplicate data with unc path

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
bryan oakley-wiggins
Asked:
bryan oakley-wiggins
  • 6
  • 4
1 Solution
 
gbanikCommented:
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
 
gbanikCommented:
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
 
gbanikCommented:
In order to eliminate everthing after that last "\"... use this formula
=MID(A1,1,FIND("|",SUBSTITUTE(A1,"\","|",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
gbanikCommented:
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
 
bryan oakley-wigginsSenior Cloud EngineerAuthor Commented:
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
 
bryan oakley-wigginsSenior Cloud EngineerAuthor Commented:
also the ColumnA unc paths are different lengths
0
 
bryan oakley-wigginsSenior Cloud EngineerAuthor Commented:
hi

ok, I have it working on 1 cell - I have about 21000 rows - how do I apply to all in the columns??
0
 
gbanikCommented:
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
 
gbanikCommented:
.....le click the right hand "bottom" corner ....
0
 
bryan oakley-wigginsSenior Cloud EngineerAuthor Commented:
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now