Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Adding apostrophe to front of number

Status
Not open for further replies.
hedgracer

hedgracer

Programmer
Mar 21, 2001
186
US
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
 
Sort by date Sort by votes
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote





"why not just format the cells?"

FORMAT changes NOTHING! The underlying VALUE remains NUMERIC.

The leading apostrophy is one way.
Code: 
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]
[glasses] When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[tongue][/sub]
 
Upvote 0 Downvote
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!}
 
Upvote 0 Downvote
>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?
 
Upvote 0 Downvote
 
Upvote 0 Downvote
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).


 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote



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

Skip,
[sub]
[glasses] When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[tongue][/sub]
 
Upvote 0 Downvote
=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]
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

bhujanga
  • Locked
  • Question
Prevent Converstion of Long Numeric Strings to Exponential Format 1
Replies
4
Views
189
remeng
remeng
Metzke
  • Locked
  • Question
How to Transfer Licence to New PC
Replies
11
Views
378
teknance
teknance
duytoi
  • Locked
  • Question
How to add a head to the search results in Excel 2016 using VBA
Replies
2
Views
1K
Andrzejek
Andrzejek
ladyck3
  • Locked
  • Question
What has happened here to Tek-Tips?? 17
2 3
Replies
42
Views
4K
Jon
Jon
Wicca
  • Locked
  • Question
MAXIFS - transfers NUMBERS but wont transfer <1.6 from one tab to another
Replies
16
Views
195
Wicca
Wicca

Part and Inventory Search

Sponsor

Back
Top