Solved

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

Posted on 2010-11-16
8
246 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 120

Assisted Solution

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
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.

821 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