Format a column as text 2

[kstargold]

I need to format a column of cells to be text. The data that is in these cells in pulled from an online form about how many people have access to a database. The options are 1-9, 10-24, 25-49, 50+. These values aren't very friendly to the general format because excel tries to read '1-9' and '10-24' as dates.

How do I make them only text?

I've looked around and all i've found is a lot of info on

<code>
columns("z").NumberFormat = "mm/dd/yyyy"
</code>

and variations henceforth.

I saw somthing on a web site about the style property, but I couldn't find much on it?

~kstargold

 

[SkipVought]

Hi,

How does the data get into your sheet?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[ETID]

When you "pull" this data, can you concatenate a ' to the beginning of this field?

 

[PHV]

Have you tried this ?
columns("z").NumberFormat = "@"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kstargold]

Skip, it is directly imported from csv format.


ETID, I could put a ' at the beginging of the line,

Cells(i, "bp").Value = "'" & Cells(i, "bp").Value

that should do it. Good idea.

PHV,
No, I haven't tried that, But I just did and it worked great.

What does the @ meen?

~Kanan

Thank you everybody for the help,
you guys saved my neck again,
I'll donate $5 in each of your names.

Thanks again,
~Kanan

 

[PHV]

What does the @ meen?
Format as Text

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kstargold]

Great,

I put some code at the top, but it doesn't work.

Is there an escape char? such as / so you can print a ' ?

Thanks,
Kanan

 

[ETID]

....Please clarify, what is it you are trying to do?

 

[SkipVought]

kstargold,

The PROBLEM is that Excel RECOGNIZED the STRING "1-9" or "10-24" as DATES and actually CONVERTS THE VALUE to a NUMERIC (37995 and 38284 respectively),

So you need a procedure to reclaim your original data.
Select the column and run

Sub ReclaimStrings()
   For Each s In Selection
      With s
         If IsNumeric(.Value) Then
            .Value = "'" & Month(.Value) & "-" & Day(.Value)
         End If
      End With
   Next
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244