Excel - Converting numbers to text 1

[jercisneros]

I am trying to convert some number in an entire row to text, I have right-clicked the file and format cells and in the category I set it to text. The problem is, I am using it as a source through an ODBC and the program that I run requires that the cell contain an apostrophe ' in the beginning of the number to force the number to become text. That is the only way that the information will print. Is there a formula or a different way to add the ' to all the numbers in that row without changing the numbers, and without changing the information one by one?

I really would appreciate your input,

-Jerry

 

[KenWright]

Sub Num2Text()

For Each c In ActiveSheet.UsedRange
c.Value = "'" & c.Value
Next c

End Sub

Then do your import

Regards
Ken................

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

 

[xlbo]

Formatting only changes the LOOK of things - not the data type

a formula could do it:
=Text(A1,"0&quot
will convert to text
OR
for each c in selection
c.value = worksheetfunction.Text(c.value,"0&quot
next

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[rdodge]

Sum ConvertNum2Text()
Dim C as Range
For Each C in Selection 'only do it to those cells that's selected
If C.Formula <> &quot;'&quot; Then 'Check to see if the cell already has an appostrophy at the beginning, and if it doesn't then add it as there is no sense in having multiple appostrophies at the beginning.
C.Value = &quot;'&quot; & C.Value
End If
Next
End Sub

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[jercisneros]

I'm sorry guys, I am not that advanced. Where do I put this code? In the Formula bar or as VB code? I'm lost.

Thanks for you quick replies,

-Jerry

 

[rdodge]

Put Ken and/or my code in VBA. Sorry, this site wasn't working again around noon EST.

Feoff does have a point, but if all you needed was to have the numbers converted to text without the appostrophy, you could have simply used Data>Text to Columns...

Go through the first 2 steps, and on the third step, select Text, and finish it out. Of course, select your range first before doing that.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.