|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How can I add a "close quote" mark AFTER each entry to a LONG column of numbers?
I'm working with a very long (380 or so) column of numbers. Each entry in the column is in this format: 0.0215
The entire vertical column is formatted "Number - 4 decimal places". I want to add the quote mark after each entry WITHOUT CHANGING ANY OF THE NUMERICAL ENTRIES, so that the above entry will appear like this: 0.0215" The numbers in the column I want to modify are meant to designate fractions of an inch, so that's why I want to add the quote mark. How can I do that? |
#2
|
||||
|
||||
Use Custom Format:
HTML Code:
0.0000\" |
#3
|
|||
|
|||
The following will add the inches symbol but if the last digit is ZERO, it is dropped from the number even though the column is formatted to FOUR DIGITS after the decimal.
Code:
Sub InsertQuotes() Dim arr() As Variant, i As Long arr = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row) For i = LBound(arr) To UBound(arr) arr(i, 1) = arr(i, 1) & """" Next i Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Value = arr End Sub |
#4
|
|||
|
|||
Quote:
Highlight your cells or your entire column. On the Home ribbon, find the section for Number formatting. I tend to first format as a Number, change to four decimal places, and then go to Custom. In the Custom pane, you can type the character you want to show behind your number. The wrinkle is the quotation mark itself is not going to work, because Excel thinks you're using a formula. So I type behind the number " (open quotation mark) then ' (apostrophe) then another ' (apostrophe), then " (close quotation mark.) The quotes frame what you want to put there, and the two sequential apostrophes appear as if a quotation mark. So, in your Custom line, it's 0.000"''". Quote-apostrophe-apostrophe-quote. |
#5
|
|||
|
|||
Depending what you want to do with the values, you could also have an additional column next to your existing one, that has, for example
Code:
=A1&""" |
#6
|
||||
|
||||
Have you tried my suggestion in post #2 ?
|
#7
|
|||
|
|||
Yeah. Given OP's desire to leave the numerical values unchanged, just changing the formatting (as you have done), is probably what he needs. For other purposes though, changing the value could be useful. For example if he wanted a textual summary statement such as
Code:
="The measurement is: "&B1 Though, as I write this, is accurs to me that Code:
="The measurement is: "&TEXT(B1,"0.000\""") |
#8
|
||||
|
||||
So my case I would have used:
Code:
="The measurement is: "&B1&"""" |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Split apart "page break" and "paragraph mark" | EwenMc | Word | 4 | 10-03-2021 02:12 AM |
One page in a document has a "full" or "long" Footnote Separator. It's only this one page, I'm lost. | taddpoal | Word | 5 | 05-10-2020 04:07 PM |
"Magic" indentation for "too-long" text-lines that are longer than one line | pGrnd | Word | 2 | 04-20-2020 03:23 PM |
Formula to format "I" column based on if "F" column row is not blank | ravl13 | Excel | 3 | 08-22-2017 12:26 PM |
Need help with conditional formatting & returning "Pass" / "Fail Results" in a Column N | mikey386 | Excel | 2 | 12-11-2014 01:14 PM |