Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2010-11-16
8
Medium Priority
?
251 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 500 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 500 total points
ID: 34147429
0
 
LVL 20

Assisted Solution

by:Ardhendu Sarangi
Ardhendu Sarangi earned 500 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 24

Assisted Solution

by:Tracy
Tracy earned 500 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
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…

972 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