Adding apostrophe to front of number

[hedgracer]

I need a formula to add an apostrophe to the front of a number (thereby converting it to text) in a cell in excel. I need to convert 2001 to '2001 without typing the ' in 11000 rows. Can someone help me on this? Thanks.

Dave

 

[randy700]

why not just format the cells?

Randy

 

[chipk]

I'm wondering the same thing, but if you want to do it the hard way, insert a row next to the numbers, use the following forumla:

=("'"&A1)

Drag that to the bottom of your 11000 rows, copy and special paste the values only.

 

[hedgracer]

Unfortunately neither of these approaches results in a hidden apostrophe (which is what I need). I have googled this thing to death and have not found a clue on how to add a hidden apostrophe.

Dave

 

[chipk]

Oh, you didn't say hidden apostrophe.

 

[chipk]

http://support.microsoft.com/kb/213440

 

[SkipVought]

"why not just format the cells?"

FORMAT changes NOTHING! The underlying VALUE remains NUMERIC.

The leading apostrophy is one way.

sub Number2Text()
'this will change NUMBERS to TEXT in any column range contiguous with the active cell
  dim r as range
  for each r in range(activecell.end(xlup), activecell.end(xldown))
    with r
      if isnumeric(.value) then .formula = "'" & .formula
    end with
  next
end sub

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[webrabbit]

Ihstead of inserting an apostrophe, insert a [160], or hex 'A0'. That character displays as a space but converts a numeric string to a text string. Use the numeric keypad with the alt key to generate the [160], i.e., press and hold the alt key while pressing 1 6 0 on the numeric keypad, then release the alt key.

I use that character when I am extracting data from a database and want to ensure that a field such as UPC is treated as text, not as a number. (Excel often converts UPCs to floating point if you let it!}

 

[strongm]

>insert a [160], or hex 'A0'

The 'hidden' apostrophe is the correct character in Excel for indicating that what follows is text. Why would you use something different?

 

[SkipVought]

The apostrophy prefix is a carry over from Lotus 1 2 3 lable formatting.



The prefix determines a label's alignment. By default, labels have an apostrophe (‘) prefix and are left-aligned. To change the alignment, type a different prefix when you make your entry. Entering a carat (^) centers your label, a quotation mark (") aligns it with the right side of the cell.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[strongm]

Sure. And they in turn were derived from VisiCalc's method of nominating labels. I'm not sure I understand the point you are making.

In Excel, only the caret and quotation mark are considered 'foreign' (as I'm sure you know, you have to ensure that Tools/Options/Transition/Transition Navigation Keys is checked before they have any prefix effect), but the apostrophe is native (indeed, its native affect gets subtly changed if Transition Navigation Keys are True).

 

[yogia]

Hi Dave:

Let us see if the following formula based approach will work for you ...

If your numeric entries are in column A startimg from cell A1 down,

then in cell B1 key-i =A1&""

to have the formula apply to all the cells down in column A, click in cell B1, and then double click at the bottom right hand corner (the fill handle) and the formula will apply to all the cells in column B down to the last matching row in column A

Now if you want to convert the formulas in column B to values, select column B, do EDIT|Copy, and then do EDIT|PasteSpecial Values, and optionally delete column A

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[randy700]

I realize that formatting doesn't change the value. However, what would be the reason for making the change if not for DISPLAY purposes. Formatting the cells will accomplish exactly that.


Randy

 

[SkipVought]

Formatting does not change a NUMBER to TEXT. Those VALUES are substantially DIFFERENT.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[Welshbird]

=text(a2) any good to you?

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[xlbo]

I guess the resolution to this will be based on what the OP wants to do with the data once it is converted to text.....is there any particular reason for wanting the "hidden" apostrophe or do you just need them converted to text? (if so then any one of 5 different replies here will work for you)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question