Coerce Number To String

[goss]

Hi all,

Using Excel 2010

I would like to coerce all values in a column to strings (text) as some records are numbers and some are mix of number and alpha characters

I would like them all as strings before export to .csv

Here is my snippet which does not appear to work
Numbers are still number and are naturally right-justified and/or formatted as 5.51538E+11

With ws
Set rng = .Range("H1:H" & lngRows)
For Each C in rng
C.Value = CStr(C.Value)
Next C
End With

 

[AlphaFrog]

You could use the Text to Columns feature to convert the entire column to text.

• Select Column H
• Select from the menu Data\ Text to Columns
  • Step 1: Fixed Width, Next
  • Step 2: Next
  • Step 3: Column Data Format=Text, Finish

 

[goss]

Thanks

Here is my final code, probably not the most esoteric, but it gets the job done.
It has the added functionality of ensuring all alpha charcters are Upper Case

strInvFormula = "=UPPER(H1)"
With wsData
Set rngInvText = .Range("M1:M" & lngRows)
rngInvText.Copy
.Range("H1").pasteSpecial(xlPasteValuesAndNumberFormats)
rngInvText.ClearContents
End With