• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

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

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
LenCepeda
Asked:
LenCepeda
4 Solutions
 
DVation191Commented:
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
 
Ardhendu SarangiSr. Project ManagerCommented:
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
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.

 
TracyVBA DeveloperCommented:
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
 
Ardhendu SarangiSr. Project ManagerCommented:
Tracey,

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

You are truly a Genius.

- Ardhendu
0
 
Rey Obrero (Capricorn1)Commented:
you can use this formula

=MID(A2,SEARCH("|",A2)+1,99)
0
 
LenCepedaAuthor Commented:
wow thanks for the quick responses.  i stepped out for lunch and you all answered my question before i got back. thanks again
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now