Adding an apostrophe to a column of numbers

Hello, I need to add an apostrophe to each cell in a long column of numbers. 
I do not want to format them as text or format them as custom - our system 
requires the apostrophe at the beginning of the cell. My question is - is 
there a way to format one cell with the apostrophe and then copy that 
apostrophe down to the other cells??  or can someone write me something that 
will allow me to do it quickly? I want to avoid having to go into each cell 
and put the apostrophe in!  Thank you in advance....
1
Utf
3/12/2010 4:15:01 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
6233 Views

Similar Articles

[PageSpeed] 38

One way. Change 9 to your column
Sub addapostophe()
mc = 9 'column I
For i = 1 To Cells(Rows.Count, mc).End(xlUp).Row
Cells(i, mc).Value = "'" & Cells(i, mc)
Next i
End Sub

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Denise C." <DeniseC@discussions.microsoft.com> wrote in message 
news:72FCA0EF-66A4-4458-B795-4BCDD12545EE@microsoft.com...
> Hello, I need to add an apostrophe to each cell in a long column of 
> numbers.
> I do not want to format them as text or format them as custom - our system
> requires the apostrophe at the beginning of the cell. My question is - is
> there a way to format one cell with the apostrophe and then copy that
> apostrophe down to the other cells??  or can someone write me something 
> that
> will allow me to do it quickly? I want to avoid having to go into each 
> cell
> and put the apostrophe in!  Thank you in advance.... 

0
Don
3/12/2010 4:28:22 PM
I would create an additional column. That way you don't lose the 
functionality of the numbers. Use a formula like:
="'"&Text(a1,"0.00")

The first part is double-quote, apostrophe, double-quote.

Regards,
Fred

"Denise C." <DeniseC@discussions.microsoft.com> wrote in message 
news:72FCA0EF-66A4-4458-B795-4BCDD12545EE@microsoft.com...
> Hello, I need to add an apostrophe to each cell in a long column of 
> numbers.
> I do not want to format them as text or format them as custom - our system
> requires the apostrophe at the beginning of the cell. My question is - is
> there a way to format one cell with the apostrophe and then copy that
> apostrophe down to the other cells??  or can someone write me something 
> that
> will allow me to do it quickly? I want to avoid having to go into each 
> cell
> and put the apostrophe in!  Thank you in advance.... 

0
Fred
3/12/2010 4:39:27 PM
I undertand that you want to change the cell contents. Use
 = "'" & A1

(first one is double quote, single quote and double quote)
Then copy the result and Paste Value to A1

Hope this helps.


"Denise C." wrote:

> Hello, I need to add an apostrophe to each cell in a long column of numbers. 
> I do not want to format them as text or format them as custom - our system 
> requires the apostrophe at the beginning of the cell. My question is - is 
> there a way to format one cell with the apostrophe and then copy that 
> apostrophe down to the other cells??  or can someone write me something that 
> will allow me to do it quickly? I want to avoid having to go into each cell 
> and put the apostrophe in!  Thank you in advance....
-1
Utf
3/12/2010 4:40:04 PM
Thanks everyone!  I used Hakyab's & Fred's method and it worked like a 
charm!! Don, appreciate the code, may try that out when I have more time.... 
really appreciate the quick replies!! Have a blessed day!!
Denise

"Denise C." wrote:

> Hello, I need to add an apostrophe to each cell in a long column of numbers. 
> I do not want to format them as text or format them as custom - our system 
> requires the apostrophe at the beginning of the cell. My question is - is 
> there a way to format one cell with the apostrophe and then copy that 
> apostrophe down to the other cells??  or can someone write me something that 
> will allow me to do it quickly? I want to avoid having to go into each cell 
> and put the apostrophe in!  Thank you in advance....
0
Utf
3/12/2010 5:47:02 PM
Glad to help. Thanks for the feedback.

Fred

"Denise C." <DeniseC@discussions.microsoft.com> wrote in message 
news:2EDA2A70-0D47-4A6C-8F9A-83F82CA66840@microsoft.com...
> Thanks everyone!  I used Hakyab's & Fred's method and it worked like a
> charm!! Don, appreciate the code, may try that out when I have more 
> time....
> really appreciate the quick replies!! Have a blessed day!!
> Denise
>
> "Denise C." wrote:
>
>> Hello, I need to add an apostrophe to each cell in a long column of 
>> numbers.
>> I do not want to format them as text or format them as custom - our 
>> system
>> requires the apostrophe at the beginning of the cell. My question is - is
>> there a way to format one cell with the apostrophe and then copy that
>> apostrophe down to the other cells??  or can someone write me something 
>> that
>> will allow me to do it quickly? I want to avoid having to go into each 
>> cell
>> and put the apostrophe in!  Thank you in advance.... 

0
Fred
3/12/2010 9:15:12 PM
Reply:

Similar Artilces:

count different instances in two columns
Hello all, I haven't been able to solve this... I have two columns in the same sheet. I would like to extract (in another sheet) all the unique elements of each column and count how many each data is repeated. Any element can appear in both column. An example would be: column 1 column 2 red red red blue blue green yellow magenta the result would be red 3 blue 2 yellow 1 green 1 magenta 1 Sorry for my english and thanks to whoever can help me with this. =COUNTIF(A:A,"red")+COUNTIF(B:B,"red") etc. -- HTH Bo...

Outlook 2000 asking for login after AD account expired
I have a user who had their AD account expire over the weekend. We got her user ID re-enabled and removed the expiration but now when she loads outlook, she is being prompted for her UID, Domain and PW. Any ideas? Also, she can get into her mail using webmail. ...

How do I locate my serial numbers on installed versions of Office 2004?
Hello: I have five licenses of Office 2004 Professional running in my studio, and five sets of install discs. Unfortunately, when we did the installs we did not label the sets of discs with their corresponding users, so I don't know which set of discs is installed on which computer. One of those five computers has just been replaced, and I need to re-install a copy of Office on the new computer - but which one? I need to check and verify which serial number is being used on each computer, so I can tell which one can be installed - but I cannot find the serial nubmers anywhere on the co...

How to format numbers as architectural fractions (1/16,1/8,1/4 )
How do I format a number to the 1/16ths, but display the answer as an architectural fraction? Example: I want to display .25 as 1/4 rather than 4/16, but want to round to 1/16 precision. Hi Jack, Simply format the cell (Format|Cells|Number > Fraction > 'up to 2 digits'). -- Cheers macropod [Microsoft MVP - Word] "Jack W" <Jack W@discussions.microsoft.com> wrote in message news:5052F55A-83E7-477A-AB7E-3E1F1C421D34@microsoft.com... > How do I format a number to the 1/16ths, but display the answer as an > architectural fraction? Exampl...

color code series by an independent column of data
In previous versions of Excel I was able to color code a series of data in a scatter plot based on another colum of data (i.e. cross plot column 1 versus 2 and color code by column 3). I can't figure out how to do that in Excel 2007. Any help? Hi, Did you use VBA code in the previous version. Maybe the security setting is currently set to disable macros. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "rockdoc69" <rockdoc69@discussions.microsoft.com> wrote in message news:9860E0EB-194A-4BF3-A529-CE34ED798702@microsoft.com... > In pre...

extracting numbers from other cells (search)
Hi All Is there a way in excel to create a search cell?? ie if i have a column with various numbers ie. each cell containing numbers with comma like 12,2,7,34,5,21 etc (say 10 cells in a coulumn with various numbers separated by commas) is there a way of extracting number from the cells which i enter as a search, if i wanted to search 1,2,3,4,5,6 from the cells is there a way excel can show me which cells contain these numbers????? my newsreader crashed just after I hit the send button--sorry if this duplicates. I'd use a series of help columns. Put your data in A2:A999 (headers in Row...

need assistance with sorting a list of numbers
I've tried to achieve this problem with formulas and lookups and if statements and conditinal formatting however was not able to achieve desired results, so asking for help since I am not good with XL marcros..if someone can start me up I can navigate myself.. There is a list.. say of numbers and there is a possiblity that numbers will be repeats like: 12345 fail 12345 fail 12345 pass 11111 fail 11111 pass 22222 pass 33333 fail 33333 fail 44444 pass I want to read say A1 (numbers) disregard the repeats and put info A2 beside it 12345 fail fail pass 11111 fail pass 22222 pass 333...

color added after click or update
I have a form that I need to add a color feature to. When the user clicks on a part of the form to go to another form I would like to shade the selected record to keep track of where they were when they return to that form. Can anyone tell me the easiest way to temporaily shade records until closing out of the form completely? Thank you. djshides wrote: >I have a form that I need to add a color feature to. When the user clicks on >a part of the form to go to another form I would like to shade the selected >record to keep track of where they were when they return to that for...

How to create chart with 1 column and 3 lines
IF This type of chart not in Standarad or Custom Types. THANK YOU Hi, Start by creating a standard column chart. Now select 1 of the column series that you want as a line. Right click and chose Chart Types. Select Line chart. That should have now changed the data series to a line. Select another column and this time press F4, which will repeat the last action. Repeat for the final series. Or maybe its easier to create a line chart of all 4 and the change 1 series to column using the same technique as above. Cheers Andy agenda9533 wrote: > IF > This type of chart not in Standara...

Generate a unique number of list
Hey all, I have a list of number which some is repeated, how can I regenerate a list with unique number. e.g 1 2 1 5 3 5 Expected Result 1 2 5 3 Thanks for any one who can help. -- Terence Chan ------------------------------------------------------------------------ Terence Chan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26963 View this thread: http://www.excelforum.com/showthread.php?threadid=467164 Hi Terence One way Mark your range of data, Data>Filter>Advanced Filter and Select Unique values only. Copy the data to a new location. Rega...

Font color for negative numbers
Help...I have created a spread sheet and I need to change the font color to red on all the negarive numbers. I tried searching for but it doesnt' work. Help ahain..thx Select some of the cells that you wish to format this way, and from the Menu Bar, <Format> <Cells> <Number> tab, And click on "Custom", Scroll down and you'll see several formats with RED in them. Select one, and using that as a start sample, you should be able to get something you like. -- HTH, RD --------------------------------------------------------------------------- Please kee...

Using Service
We’ve been using CRM 1.2 Sales Professional. We want to use the CRM Customer Service product (actually Suite Professional) now but our service mgr is hung up on one requirement. Currently, when a customer sends a request or question to support@ourcompany.com, they get a standard template email BUT it has a ticket number in the subject line. I’ve been told that we must have this functionality with CRM. I’ve also been told that this is a complex customization that would not be easy. This seems like very basic, common functionality. Is this really a complex customization? Ple...

Need top record for each contract number
Hi everyone. I'm using A02 on XP. I have a query with: SELECT [Enrollment Table].[Plan #], [Enrollment Table].[Date Received], [Enrollment Table].New FROM [Enrollment Table] WHERE ((([Enrollment Table].New)=Yes)) ORDER BY [Enrollment Table].[Plan #], [Enrollment Table].[Date Received] DESC WITH OWNERACCESS OPTION; This pulls all the 'New' Enrollment records. There could be 560 records for one contract and only 2 for another. I need to pull only the most recent [DateReceived] for each [PlanNum]. Can't figure it out. Tried Max, tried Top but sure I'm doing somethi...

Pivot Table
Hi, I have a Pivot table with some numbers in it. Is there a way to replace the numbers with a text? For example: replace all numbers >1 with "yes" . Is this possible? If not whats the best way to achieve this? Regards, Vinstream Only by changing your data source. Eg. Sub Test() Dim rCell As Range For Each rCell In Range("DataSource").Columns _ (2).SpecialCells(xlCellTypeConstants, xlNumbers).Cells If rCell > 1 Then rCell = "yes" Next rCell End Sub "vinstream" <vinstream@gmail.com> wrote in mes...

Need to convert multiple phone number styles in CRM 3.0
Hi, I have imported phone numbers from many different sources and they are all formatted a little different. Ex: 123.456.7890, (123) 456-7890, 123-456-7890, etc. I need to convert them to one format like (111) 222-3333 x44444. I have run into a wall in attempting to do this. Any ideas would be greatly appreciated. -- Thanks, Andrew Hallock The Primas Group Well, first, you need to put phone formatting JScript into your forms to ensure ongoing edits are formatted in a consistent manner. Then, you need to tackle the exisitng data. This can be done in a variety of manners, but you wil...

Displaying Pictures in Columns on a Form
I have a form which displays (1) Teacher Name and (2) Teacher Picture. On the same form, I have a Subform with pictures of all the Students associated with the particular Teacher. Each pic of the Student shows, BUT, if there are more than 4 or 5 students, the pictures begin to scroll. Is there a way that I can have 2 columns of pictures so I can dispay maybe 8 or 10 student pics, then possibly with a NEXT button to move to the remaining student pics for that Teacher if there are any? I can't think of a clean way. And, I have to assume you using a2007...as the ability to display pi...

Number of Pages: A Better Way?
Word 2003 Windows Vista Business I'm trying to determine programmatically the number of pages in one document for use in another document. I've worked out that: NumberOfPagesInOtherDoc = OtherDoc.ActiveWindow.Panes(1).Pages.Count gets me what I want, and it is probably robust enough for my current needs. However, I'm wondering if there isn't a more efficient way. I recognise that the concept of "the number of pages in a document" is a somewhat fluid thing - influenced by stuff like the printer driver, etc. - but I somehow expected there to be a mo...

saving to .csv and preserving numbers as text
I am trying to export from Excel to .csv and want to preserve data such as "1100.000" just as it is without loosing any digits. Any ideas? Bill, This should work! I realize you posted in .misc, but a macro is all I could come up with! Sub ExportCSV() Dim fs, f OutFile = Application.GetSaveAsFilename(FileFilter:="CSV (Comma delimited)(*.csv), *.csv") If OutFile = False Then Exit Sub ActiveSheet.UsedRange.Select Set fs = CreateObject("Scripting.FileSystemObject") Set f = fs.OpenTextFile(OutFile, 2, -2) For Rcount = 1 To Selection.R...

total of column
I�m trying to make a check register, and I wont to make that the tota of the balance column should be on top of the spreadsheet, the balanc column is a total from each row and I need the last sell from thi column should be on to -- Message posted from http://www.ExcelForum.com Hi in A1 enter (if no blank rows are in between): =OFFSET(A$2,COUNTA(A$2:A$1000)-1,0) and copy this to the right -- Regards Frank Kabel Frankfurt, Germany > I�m trying to make a check register, and I wont to make that the total > of the balance column should be on top of the spreadsheet, the balance > col...

Format custom number: C-0000-L
Hi, I need to format a few cells in Excel to look like C-0000-L where "0000" represents four numbers and "L" represents a letter. I tried to do it in format - custom with C-0000-@ but it gives me an error message. I also tried to do it using the concatenate function but it doesn`t work. Can anyone please help me? Thanks! -- Sonia Custom format "C-"0000"-L" -- David Biddulph "Sonia" <Sonia@discussions.microsoft.com> wrote in message news:8EAB02FD-1AA5-4E80-880A-7C9A33A8FE01@microsoft.com... > Hi, I need to format...

sum of columns
How do you make column the entire column c equal the entire column a + the enitre column B for each corresponding row? ie. c1 = a1 + b1, c2 = a2 + b2, c3 = a3..... and so on so forth. I've figured this out on an individual basis but I'd like to apply this to the entire colum, with out having to code each cell individually. Hi See http://computing.fandm.edu/training/excel/autofill.html on how to fill the C1 formula down. HTH. Best wishes Harald "chusome" <matt.tremblay@gmail.com> skrev i melding news:1118241671.104209.230800@f14g2000cwb.googlegroups.com... > How...

Defined range using more than one column
Can anyone tell me if it is possible to have two or more columns in a defined range. I want to have a dropdown validation list where two or more columns of data is displayed on the dropdown list. When an item is selected in the dropdown list only one of the columns data is displayed in the cell. Is this possible and how is this accomplished? Or if there is a different approach I should go down please let me know. Thanks Pat You can have a defined range with two or more columns, but that won't show up in Data=>Validation using the list option. You might want to look at the Cont...

Auto Number #3
In my spreadsheet I have a cell called file number. Is their anyway that a can get this cell to automatically put in the next number everytime I enter new data, so that I do not have to type in the number it is excel 2000 If your numbers are in column A, maybe you could just put a bunch of formulas in that column that depend on another column having data. =if(b10="","",a9+1) (in A10, and drag down.) horseman wrote: > > In my spreadsheet I have a cell called file number. Is their anyway that a > can get this cell to automatically put in the next number everyt...

Adding + in front of a number
I have a column of numbers which represent a change in value over the previous year. I want to have a + in front of the number if it's a positive change. Let's say my first number is 500. My problem is that I can write something like, if(a1>0,"+"&a1), but when then I can't format the number. If the number is in the millions I can't provide a comma as a thousand separator and I cannot tell it how many decimal places I want to show. Is there a really easy way to do that, or do I have to write a huge formula to precisely spell out the formatting within...

Adding documents to "File"
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I the Top line of word, where the apple logo, Word-File-Edit and so on. How do I get the &quot;last 6 documents&quot; that I have recently opened to appear when you clicl on &quot;File&quot;? In article <59bb8f2b.-1@webcrossing.JaKIaxP2ac0>, <billyboy@officeformac.com> wrote: >Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I the Top >line of word, >where the apple logo, Word-File-Edit and so on. How do I get the "last 6 >documents" that I have recently open...