Every week, I receive a flat file with three columns in this format:
2008-11-03 15:26:53, 'jon@abc.com', 'MYSPACE'
2008-11-03 15:14:02, 'jenroy@abc.com', 'FACEBOOK'
I use "text to columns" in excel to separate this into three columns.
But I can't find a fast way to eliminate those single quotes that are around the email address and the last column.
I am importing a csv file and each field in this file is imported with an apostrophe
eg: '01
I need to remove the apostrophes but leave the information as text thereby keeping my leading 0's
have tried formatting as text
data text to columns
nothing I do works.
I have a sheet with apostrophes before the data in each cell, apparently this marks the data as text? I would like to remove the apostrophes without editing each cell individually, the data that would be left behind would have the format H12345. I hjave tried lookup and replace, I have tried LEFT,A1,5 but when copied and pasted back as a value the apostrophe re-apperas.
View 9 Replies View RelatedIf I manually remove the apostrophe, enter out of the cell, and select it again, the apostrophe is back.
View 12 Replies View RelatedThis question is about punctuation.
I know the following is a correct way to reference a question mark and a period....
We have our system set up with 2 folders one with blank forms and the other with these forms filled out and completed (records), is their a trick to removing the data from a completed form fast with out removing tables and text used to make the forms questions? If I could do this it would save time from opening in the blank forms folder then saving in the records folder.
View 4 Replies View RelatedI have a workbook that has ben imported into excel and I need to extract the Cabinet sizes (red text) from ColumnC On Sheet2 leaving out the rest of the info. I have a button on the home sheet that has the code that I am running to generate this report it will do most of what I want but I am stuck at extracting the cabinet sizes.
The data is coming from the SheetComponetListing worksheet and going to Sheet2. I have manually created the end result that I am looking for on the CabinetSize worksheet. [URL]....
If I run this vba it removes the external data connection to the pivot table:
ActiveWorkbook.Connections("EXAMPLE").Delete
When I attempt to drill down into the pivot table it states the data connection was removed.
Is there a way to remove the data connection but preserve the data in the pivot cache?
It's probably possible to store the data somewhere else in the document and link the pivot table up to it but that seems cumbersome.
I Want to Delete Data.
Filter I Column And Delted "N" ....
I was messing around with data connections and importing stuff from a network in real time. Cool stuff. But everytime I open ANY excel worksheet on this computer, it tells me "file cannot be found" and refers to one of the first files I used for this. this happens no matter what workbook I am opening.
When I click data --> connections its empty.
I have data like this....
6
4
5
2
.......in the first column A1:A4
I would like to add and remove data to the column so I have data that would look like this...
6
4
5
2
1
9
.......in the first column A1:A6
I would like to find the average on differing numbers of data without changing the formula. So how can I tell the software that my average has now changed from 4 data to 6?
Trying to select all cells below the last row of data based on column B and remove borders.
So far I have this.
[Code] .....
Suppose I have data in a range that was imported from "somewhere". Some of the data is preceded by team rankings in parenthesis...like so:
Alabama
(21) Boston College
Notre Dame
(10) Oklahoma
Texas
Florida State
(2) Florida State
I want to strip the data of the team rankings, (as well as any brackets and spaces before the team name). Is there a macro I could write to do this?
I have a list of Surnames in Column D, e.g. ANDERSON, BROWN, COOPER, but for some reason, who ever input the data, decided to include the salutation after that Surname, in brackets, where it was available, e.g. ADAMS (Miss), BUTTON (Mr & Mrs), COX (Dr).
So I'm trtying to remove everything within the brackets (if there is anything) and place it in Column C. I will then Replace the brackets and Trim the contents in Column D. Here is what I want the code to do
I have data I extract out of a datasource. I run a few excel scripts upon it and export the content to another spreadsheet. The column of data (I have columns a - h) i'm concerned with is H. The totals in that column regardless of the length of the column (could be 10 rows of data or 10,000) I need to delete the top 10% and bottom 10% rows completely of those numbers in H and leave the remaining 80% in tact.
View 2 Replies View RelatedI need to remove any attribute that doesn't have a spec in it and keep the attribute that does have a spec in it.
Width=5/8 in|Thickness=0.0150 in|Length=100 ft|Tension Cap.=750 lb [Max]|Inside Diam=|Material=Austenitic Stainless
Steel|Coating=|Color=|Locking Type=Valuclips?|Used With=|Type=|Quantity=|Wt.=3.60 lb
I need to remove for example Inside Diam=| since it doesn't have a spec about the product
Width=5/8 in| will remain since it does have a spec about the product
so after this is done it should look like this
Width=5/8 in|Thickness=0.0150 in|Length=100 ft|Tension Cap.=750 lb [Max]|Material=Austenitic Stainless Steel|Locking Type=Valuclips
|Wt.=3.60 lb
by selecting any cell in sheet 2 column G it will then copy 4 cells to the left paste them to sheet 3.
Then delete the selected row in sheet 2
i have data exported as CSV from in house system, the problem is that data is separted by commas therefore some of cells have split, i need VBA to remove the commas and bring my data back into correct format. Below is a sample of what the data looks like, real data is 5000 rows of data
PFOLIO A/C CODE ACCT NAME CUR CODE DESCRIPTN NARRATIVE DATE ENTRY DATE CASH VALUE De --------------------------------------------------------------------------------------------------------------------------------------------ACA001AUDCUST JP MorganAUDINT0005 SALE 39727.08 INTECH BAL GR FD C29-Dec-082-Jan-09-27690.57N
I have found a very useful UDF for removing non-alpha characters from strings. (See below, Credit for posting to Stanley D Grom - Ozgrid post ´Removing Non-alpha Characters From Text´).
Option Explicit
Private Function RemoveCharacters(InString As String) As String
Dim intLoopCounter As Integer
Dim intStringLength As Integer
Dim intASCIIVal As Integer
intStringLength = Len(InString)
InString = LCase(InString)
For intLoopCounter = 1 To intStringLength
intASCIIVal = Asc(Mid(InString, intLoopCounter, 1))
If intASCIIVal >= 97 And intASCIIVal <= 122 Then
RemoveCharacters = RemoveCharacters + Mid(InString, intLoopCounter, 1)
End If
Next intLoopCounter
End Function
Two requests:
1. Could the UDF be modified such that any part of a string contained within brackets is also removed (e.g. "NLGA High Street (West-Enfield), EN6" becomes "nlgahighstreeten")?
2. Can an argument be added to the format of the UDF, such that numbers (0 to 9) are either included or excluded (e.g. RemoveCharacters(A1,1) where the argument ´1´ would include any numbers (0 to 9), so "NLGA2003 High Street (West-Enfield), EN6" becomes "nlga2003highstreeten6")? ´blank´or ´0´would exclude these numbers, i.e. would return "nlgahighstreeten"
I am trying to create a summary sheet that updates when input data is added.
I'm still learning many parts of excel and I'm unsure why the SUMIF function on occasion leaves duplicate data in.
I can live with the fact that I will need to correct the input data and am willing to just paste in corrected data when it has been validated via the data validation tab.
Once this has been done, all I am seeking is for a subsequent process to start which updates a summary sheet with the sums correctly adding via the sumif function.
I have left some instructions on the actual spreadsheet to clarify my intended results.
I have a data import from our client something like this:
a1:12/11/2009 shoe bags blank-** $700.00 $0.00 $3,949.00
How can i delete all the numeric data in the cell?
i need only the text: shoe bags blank-** in a2
I have a lot of data in column "G" & I need to only remove "<object height=" To "</object>"
See the attachment
In the enclosed SS there are several columns, "A" being Style and "G" being Available. I'm trying to piece together some code that will remove all of a style(all sizes) if the number of stock is less than say 3; however, if any one style has 3 or more available the entire style needs to remain in the spreadsheet.
I've been trying to piece together some code but I don't think I've quite come up with what I'm looking for.
I have exported a aging report from SAL to excel. Now the problem is each figures in the report is not in number format. when i checked each cell contains a space after the numbers so excel does not treat them as a number format.
how i can remove all the spaces in those cells. find and replace doeasnt work.
i have the following sample data in which there maybe duplicates in each row, i need a code that will remove the duplicates.
So from this
Date hearder Row is J1
BHPRIOWOWBTRTRTYRTYLOIRTYTELTELSPA456123456123
To this
BHPRIOWOWBTRTRTYLOITELSPA456123
i need to remove this all bracket & data.answer should like this.
ex-
column "A"
AS-18881(C-8567)
SP-25768(G-856)
AS-25687(C-687)
ex-
column "A"
AS-18881
SP-25768
AS-25687
I need to be able to take a string & remove all non numeric data. If I had "(123) 456-7890" I would want it to return "1234567890".
View 6 Replies View RelatedI have used the following VBA quotes for my workbook to remove auotfills and data that I want to remove from my worksheet. The workbook has multiple worksheets and the worksheet that I want to use this VBA on is on worksheet 10 (ie. Sheet10). what's wrong with the VBA codes that I have below? Currently nothing happens whenever I try to click on the button that's linked to this code and there's no error message.
Sub ClearStuff()
Dim rng As Range
For Each rng In Sheets(10).Range("C18:BV" & Sheets(10).Range("B65533").End(xlUp).Row)
If rng.Value = Sheets(10).Range("D11").Value Then
rng.ClearContents
rng.Interior.ColorIndex = xlNone
End If
Next rng
End Sub
I have spreadsheet with some very basic formulas that work out sale prices based on people entering buy prices. (It works out the % margin). There are certain areas where the user enters the data - and does not touch any other part of the sheet.
What I am trying to do is add a button which will remove the data from these cells. Almost - a start again button or refresh button. This way it will remove previously entered data - without removing formulas from the cells where the calculation happens. So basically there are cells that a user will enter data - and it is these cells I would like to be able to clear with a button or similar without affecting other cells.
I'm hoping to achieve the result in the image below.
Column A contains data, column B contains values to be added to column A and column C contains values to be removed from column A. Using formulas or macros, how can I present the result in column D?