Excel Number to Text Macro

I had a pretty hefty spreadsheet of contacts that I wanted to post into Outlook. The problem is that the phone numbers were stored in Excel as numbers and Outlook doesn’t like importing numbers into that field. Not sure what the folks at Microsoft expected, perhaps they are still living in the days when people used letters for the first digits of the phone number.

In any case, I needed to switch those number fields to text fields. And it was a royal pain to do it manually. So I came up with this little macro that did the trick.

First, it changes the value of “x” to be how many rows you want to process (6, 12, 144, whatever). Next, highlight the cell at the top of that column. Finally, execute the macro. It’ll run through each cell in the column, changing each one from a number field to a text field. Enjoy.

Here it is if you’re like me and don’t wan to type it out for yourself:

  Do
   ActiveCell.FormulaR1C1 = "'" & CStr(ActiveCell.Value)
   ActiveCell.Offset(1, 0).Select
  Loop Until ActiveCell.Row = x

Posted

in

Comments

11 responses to “Excel Number to Text Macro”

  1. Yogesh Nair Avatar
    Yogesh Nair

    I need to extract the number from the below text with a simple macro.
    AEDAM3A10Y=
    AEDAM3A1Y=
    AEDAM3A2Y=
    AEDAM3A3Y=
    AEDAM3A4Y=
    AEDAM3A5Y=
    AEDAM3A7Y=
    AUD1MB10=
    AUD1MBF=
    AUD1MO=
    AUD1MR10=
    AUD1MRR=
    AUD1YB10=
    AUD1YBF=
    EUR10F10Y=
    EUR10F5Y=
    EUR15F10Y=
    EUR15F5Y=
    EUR1F10Y=

    The output should be something like tenors for each of the above e.g. 10Y, 2Y, 5Y, 1M etc. Can this be done?