apostrophe madness

[Guest]

I have an application in which I must transfer an Excel spreadsheet to an AS400. For the end application, some of the columns must be formatted as text, but may contain numeric values. We've found that the only way to do this is to select the cell then insert the apostrophe into the formula bar. That way it doesn’t show up in the cell. It works, but it’s awkward. Is there anyway to do this through a macro?

 

[Guest]

This might work. Change the cell format for all cells you want the apostrophe to look like the following

"'"0 (thats a double quote, an apostrophe, and another double quote
then save your file as a text file. The text file now has an apostrophe before all of the numbers

----- jamipn wrote: ----

I have an application in which I must transfer an Excel spreadsheet to an AS400. For the end application, some of the columns must be formatted as text, but may contain numeric values. We've found that the only way to do this is to select the cell then insert the apostrophe into the formula bar. That way it doesn’t show up in the cell. It works, but it’s awkward. Is there anyway to do this through a macro?

 

[Dave Peterson]

Does this mean you need the leading 0's for you text import?

If yes, maybe insert a helper column and use a function to format the number the
way you like it:

=text(a1,"00000")

Copy|Paste special|values and delete the original column.

 

[Guest]

I'll give it a try. No, I don't need leading zeros. So far, the only thing that seems to work is if the apostrophe appears in the formual bar (not in the cells). Even if I format the entire column as text, cells with pure numbers wind up as numeric values in the AS400.

 

[Dave Peterson]

If you don't need the leading zeros and the cells are formatted as General,
maybe a formula like this:

=TEXT(A1,"general")

would work better.

If that doesn't work, maybe you should insert those apostrophes!

Using a macro would make life easier. Just select your range of numeric
constants and run this:

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants, xlNumbers))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Please select a range with numeric constants in it!"
Exit Sub
End If

For Each myCell In myRng.Cells
myCell.Value = "'" & myCell.Value
'maybe mycell.text to preserve formatting
Next myCell

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Guest]

Dave

Thanks again for your suggestions, I'd love to get this in a macro since many of us use this regularly. In the meantime, I found a very nice Add-in written by a colleague that works very nicely. Any idea how I can incorporate an Add-in into a macro?

 

[Guest]

Dave

The macro worked beautifully. I was able to integrate it into an existing macro so I killed 2 birds with one stone! By the way, I put your name as the author of the macro, in case anyone asks! Thanks

p

 

[Dave Peterson]

Remember to save your file before you run a macro. There's usually no Edit|Undo
and you wouldn't want to close without saving (and lose any previous changes).

Glad you got it working.