How to store numbers as text?

[dpdoug]

I have a column in Excel that has a mixture of cells that are text and numbers. I want all the values of the cells in the to be stored as text and NOT stored as numbers.

In otherwords, I need the cells that contain numbers to have the little green triangle in the upper left corner that says 'Number stored as text' when you click on it.

I need the numbers to be processed as text -- otherwise the numbers will be skipped over if they are stored as numbers.

 

[AnotherHiggins]

Highlight the column, right click and select Format Cells, select TEXT.

[tt]_____
[blue]-John[/blue]
[/tt][red]"If you're flammable and have legs, you are never blocking a fire exit."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[sawedoff]

I'll assume the little green triangle you refer to is an XP or 03 feature, since I don't have it. You should be able to convert your numbers to text by formatting the column to text, then doing a copy - Paste Special - Values for the whole column.

Sawedoff

 

[SkipVought]

Hi,

You can be sure if you prepend the value with an [apostrophy].

Here's a little procedure that you could run on any particulat column

sub MakeNumbersStrings()
   'select the column to convert
  const TIC = "'"
  with selection
    c = .column
    for r = 1 to .rows.count
       with cells(r, c)
         if isnumeric(.value) then _
            .value = TIC & .value
       end with
    next
  end with
end sub

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[dpdoug]

I'm sure that would work, but when I insert the values to a database table, it would insert the number with an apostrophe, wouldn't it?

If so, I guess I would have to test the first character in that particular field for a "'".

-------------------

Also, in response to the other post, formatting the column to text doesn't store the values as text. The provider reads the first few rows and decides if the whole row is text or numeric. If it finds text, it decides that the column is text and wherever it finds a number, it just skips over it no matter how it is formatted. The number has to be stored as text, not merely formatted as text.

 

[SkipVought]

the leading [apostrophy] is a formatting character in Excel

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[VE]

Don't know if this will help you or not, but I have some excel spreadsheets which are linked to MS Access. What I had to do to force the feild to be read as text by access (and not skipped or whatever it was doing) was that I created another column in the excel spreadsheet that added the appostrophe and hid it from the user. Then made a query in access from the linked spreadsheet that took it back off and put in an access table (it does other stuff to other fields) in the correct format. Annoying, but it solved my number/text problem.

 

[123FakeSt]

You can use another column for example B and use the formula =TEXT(A1,"00000") to force it to text, given that the length is fixed.

I needed this once importing 5 digit inventory numbers into Access. It thought 00000-99999 were numbers and A0000 on were strings and messed up my reports.

The early bird gets the worm, but the second mouse gets the cheese.