Simple - Force Change Number to Text

[Curben]

ok, I need a series of numbers changed to text, if i just change the format the cell will still be a number until i open the cell for editing and then leave the cell again.

How can I force the update on a selection to update to the text version of a number using VBA?

 

[rafaelspeixoto]

No need for VBA... try using =""&A1

 

[Smitty]

You can select the range you formatted and use Data-->Text to Columns-->Finish to apply the format to the entire range.

HTH,

 

[Curben]

I do need VBA so i can do this to 500 files in a batch instead of opening up 1 file at a time and doing this.

 

[GTO]

ok, I need a series of numbers changed to text, if i just change the format the cell will still be a number until i open the cell for editing and then leave the cell again.

How can I force the update on a selection to update to the text version of a number using VBA?

I do need VBA so i can do this to 500 files in a batch instead of opening up 1 file at a time and doing this.

Greetings Curben,

Might I suggest that your first post simply infers a selection of cells in a row/column, whereas your latest is markedly different. Could you paint us a clearer picture (I have 500 text files/workbooks/.csv's in one/several folder(s), that I want to...) as to what we are trying to accomplish?

 

[Curben]

i need the vba for JUST what i said to go in existing code that will select a few cells or a cell and force the change from general to text. since the data is a number (but not all the exports it compares to are formatted as numbers) just choosing Format Cells > Number > Text is not enough on its own. the cell has to be edited as well. Simply clicking in the cell to edit (or selecting the cell and pressing F2) and just hitting Enter will cause it to finally update.

I am trying to figure out how to get this update of a cell or cells to happen within the VBA I already have going.

This is why I asked for JUST what i needed help with w/o explaining the full project but answers akin to "you dont need VBA" are incorrect.

 

[rafaelspeixoto]

Did you try this?

Sub Macro1()
        Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 2), TrailingMinusNumbers:=True
End Sub

 

[Curben]

Tx, with some tweaking that seems to have worked.