Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Apostrophe In Data Downloaded
I am importing into Excel a text file where one particular column has some
records with an apostrophe ("'") in front of them. Not all the records in that column have the apostrophe in front. The apostrophe acts as a formatting feature as it actually does not visually appear in the cell. Only by pressing F2 could one see that the apostrophe is there. (This is probably a quirk created by the ERP system where I get the data from.) How can I get rid of the apostrophe? I tried using the LEFT function and capturing everything starting from the second character, but it does not work... Thanks. -- tb |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Apostrophe In Data Downloaded
Insert and run this small macro:
Sub tic_killer() For Each r In ActiveSheet.UsedRange If r.PrefixCharacter = "'" Then r.Value = r.Value End If Next End Sub If you are not familiar with VBA see: http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Gary's Student gsnu200703 "Tiziano" wrote: I am importing into Excel a text file where one particular column has some records with an apostrophe ("'") in front of them. Not all the records in that column have the apostrophe in front. The apostrophe acts as a formatting feature as it actually does not visually appear in the cell. Only by pressing F2 could one see that the apostrophe is there. (This is probably a quirk created by the ERP system where I get the data from.) How can I get rid of the apostrophe? I tried using the LEFT function and capturing everything starting from the second character, but it does not work... Thanks. -- tb |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Apostrophe In Data Downloaded
Thank you for your suggestion!
Just out of curiosity, is there a way to do it via a function rather than a macro? -- tb "Gary''s Student" wrote in message ... Insert and run this small macro: Sub tic_killer() For Each r In ActiveSheet.UsedRange If r.PrefixCharacter = "'" Then r.Value = r.Value End If Next End Sub If you are not familiar with VBA see: http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Gary's Student gsnu200703 "Tiziano" wrote: I am importing into Excel a text file where one particular column has some records with an apostrophe ("'") in front of them. Not all the records in that column have the apostrophe in front. The apostrophe acts as a formatting feature as it actually does not visually appear in the cell. Only by pressing F2 could one see that the apostrophe is there. (This is probably a quirk created by the ERP system where I get the data from.) How can I get rid of the apostrophe? I tried using the LEFT function and capturing everything starting from the second character, but it does not work... Thanks. -- tb |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Apostrophe In Data Downloaded
A formula can only return a value, so it cannot change
the formula or content. So the answer to your question is NO. For the difference between formulas and macros see Chip Pearson's Macros And Functions http://www.cpearson.com/excel/differen.htm -- HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Tiziano" wrote in message ... Thank you for your suggestion! Just out of curiosity, is there a way to do it via a function rather than a macro? -- tb "Gary''s Student" wrote in message ... Insert and run this small macro: Sub tic_killer() For Each r In ActiveSheet.UsedRange If r.PrefixCharacter = "'" Then r.Value = r.Value End If Next End Sub If you are not familiar with VBA see: http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Gary's Student gsnu200703 "Tiziano" wrote: I am importing into Excel a text file where one particular column has some records with an apostrophe ("'") in front of them. Not all the records in that column have the apostrophe in front. The apostrophe acts as a formatting feature as it actually does not visually appear in the cell. Only by pressing F2 could one see that the apostrophe is there. (This is probably a quirk created by the ERP system where I get the data from.) How can I get rid of the apostrophe? I tried using the LEFT function and capturing everything starting from the second character, but it does not work... Thanks. -- tb |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Apostrophe In Data Downloaded
Tiziano wrote
Just out of curiosity, is there a way to do it via a function rather than a macro? In an empty cell put a 1, Edit|Copy, select all cells with apostrophies, Edit|PasteSpecial Multiply -- David |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Apostrophe In Data Downloaded
On Feb 4, 10:41 am, "Tiziano" wrote:
I am importing into Excel a text file where one particular column has some records with an apostrophe ("'") in front of them. Not all the records in that column have the apostrophe in front. The apostrophe acts as a formatting feature as it actually does not visually appear in the cell. Only by pressing F2 could one see that the apostrophe is there. (This is probably a quirk created by the ERP system where I get the data from.) How can I get rid of the apostrophe? I tried using the LEFT function and capturing everything starting from the second character, but it does not work... Thanks. -- tb Hey Tiz, I had the same problem once before Lets assume the value is in cell A1, you can use =RIGHT(A1,LEN(A1)-1) LEN will calculate the length, and then you can use that minus the one to get rid of the leading apostrophe Some manual checking might be required from time to time to ensure that the formula's will always work for you and go all the way down the range you are using Hope this helps you |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Apostrophe In Data Downloaded
If either in A1 I have '123 or 'abc
If I enter (in B1) =Len(A1) I get 3 (the ' is not considered in the count). If dealing with numbers only I'd just use (in B1) =A1 + 0 " wrote in message oups.com: On Feb 4, 10:41 am, "Tiziano" wrote: I am importing into Excel a text file where one particular column has some records with an apostrophe ("'") in front of them. Not all the records in that column have the apostrophe in front. The apostrophe acts as a formatting feature as it actually does not visually appear in the cell. Only by pressing F2 could one see that the apostrophe is there. (This is probably a quirk created by the ERP system where I get the data from.) How can I get rid of the apostrophe? I tried using the LEFT function and capturing everything starting from the second character, but it does not work... Thanks. -- tb Hey Tiz, I had the same problem once before Lets assume the value is in cell A1, you can use =RIGHT(A1,LEN(A1)-1) LEN will calculate the length, and then you can use that minus the one to get rid of the leading apostrophe Some manual checking might be required from time to time to ensure that the formula's will always work for you and go all the way down the range you are using Hope this helps you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Manual control of link updating for downloaded quotes? | Excel Discussion (Misc queries) | |||
macro | Excel Discussion (Misc queries) | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
ranking query | Excel Discussion (Misc queries) | |||
Inserting a new line when external data changes | Excel Discussion (Misc queries) |