Solved

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

Posted on 2010-11-16
8
244 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
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 119

Assisted Solution

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

Assisted Solution

by:pari123
pari123 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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:pari123
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 119

Expert Comment

by:Rey Obrero
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
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…

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now