Solved

How do I remove charaters or split up a column in excel?

Posted on 2010-11-16
8
247 Views
Last Modified: 2012-05-10
Hi folks, i am having a hard time with the results of a sql query.  Basically i have a data field with two types of information combined into one field seperated by a pipe,  the problem is a date is included in the field and i need to be able to sort and filter by this date.

unfortunately saving as a csv makes a mess of everything.  so i copy the results and paste them into an excel file but i need to separate the that one column.

my data appears like this:
(code letters) | 11/16/2010

Example:
(SG) | 11/16/2010

I need to either remove the code letters all together, including the parenthesis and pipe or I need to split up the column into two columns.  anything suggestions would help.  


0
Comment
Question by:LenCepeda
[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
  • Learn & ask questions
8 Comments
 
LVL 20

Accepted Solution

by:
DVation191 earned 125 total points
ID: 34147396
I believe the text to column feature could help you here. Use the example posted here but instead of using the space as the delimiter, use the pipe.
http://www.worldstart.com/tips/tips.php/1153
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 125 total points
ID: 34147429
0
 
LVL 20

Assisted Solution

by:Ardhendu Sarangi
Ardhendu Sarangi earned 125 total points
ID: 34147437
Hi Len,

Here are the steps you need to follow -

1. Select the Column with your data in it.
2. Go To Data and Select Text to Columns
3. Select "Delimited" Radio Button on the first pop-up and click Next
4. On the next page, enter "|" in the Other textbox.
5. Click Next
6. Finally if you don't want to see the Codes, then select the column and click "Do not import Column(skip)" radio button.
7. Click on "Finish".

You should have the desired results

Thanks,
Ardhendu
0
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!

 
LVL 24

Assisted Solution

by:broomee9
broomee9 earned 125 total points
ID: 34147438
Yes, you can use the Text to columns as DVation191 explained, or as your question suggests, you can use a replace to remove everything before the pipe.

To replace everything, highlight the column and press Ctrl + H.
Then in the Find what box typ "*| " without the quotes (that's an asterisk, a pipe then a space)
Leave the replace with box empty and click replace all.

This will remove everything before the pipe including the space after it.
0
 
LVL 20

Expert Comment

by:Ardhendu Sarangi
ID: 34147469
Tracey,

Thats a neat trick :) I doubted the validity of that but it does work!

You are truly a Genius.

- Ardhendu
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34147660
you can use this formula

=MID(A2,SEARCH("|",A2)+1,99)
0
 

Author Closing Comment

by:LenCepeda
ID: 34149048
wow thanks for the quick responses.  i stepped out for lunch and you all answered my question before i got back. thanks again
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
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.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

759 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