Solved

Excel extract data

Posted on 2014-07-21
4
221 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

828 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