Solved

Excel extract data

Posted on 2014-07-21
4
224 Views
Last Modified: 2014-07-21
I have data in Column A excel in the format:
aaa\bbb\ccc\ddd\123.xls

I want two things to be done from this data:
1. I want to extract everything from column A before last \ , that means the formula should give result: aaa\bbb\ccc\ddd
2. After this result is in new column, let's say the formula is pasted in Column E, I should be able to compare the whole column E & take out unique values out of it.

Thanks,
A
0
Comment
Question by:Ackles
  • 3
4 Comments
 
LVL 11

Author Comment

by:Ackles
ID: 40209405
Can someone answer, I need it bit urgent....
0
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 40209643
Try this formula:

=LEFT(A1,FIND("#",SUBSTITUTE(A1,"\","#",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))-1)

where A1 contains original.

Then copy the new column, paste special >> Values over itself.

Then while that column is selected go to Data tab, and select Remove Duplicates.
0
 
LVL 11

Author Comment

by:Ackles
ID: 40210849
Great!!!


Thanks a lot.

A
0
 
LVL 11

Author Closing Comment

by:Ackles
ID: 40210851
Perfect!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

733 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