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
Comments
11 responses to “Excel Number to Text Macro”
I want to know how to read a number
i.e, 1234 as One Thousand Two Hundred and Thity Four in Excel using functions.
I want to know how to read a number
i.e, 123 as One Hundred and Twenty Three in Excel using functions.
After having a similiar problem, I saved the spreadsheet as a tab delimited text file, then imported the text back to Excel which changed the original values to the display values.
In the loop call
ActiveCell.Value = CStr(ActiveCell.Value)
Now that’s a good one. Though odd. For instance, put 123 (no quotes) in C1. Then your formula in D1. then copy column D to column E and paste special, values, then finally enter ‘123 (manually enter the tick) in F1. E1 and F1, while they look as if they are stored the same, are processed by Excel differently. E1 just sits there, and you can see the tick in the display. F1, meanwhile, shows only the number. It does have an error saying that it’s a number stored as text, but E1 doesn’t have that. Strange indeed, but I suspect that it would work okay. I probably won’t try, since it’s already done. But that is a good one. Thanks!
You could also have done this formula:
=”‘”&C1
Then copy the formula down and do the rest.
No, I understand what you mean, and for the individual column, I understand that you don’t have to change it. But the relative cell thing in Excel always causes me some problem (moving from one column to another). So when I moved it and needed it to be D1 instead, I would have to change it. Nonetheless, if that was the only issue, it wouldn’t have been a big deal. Far larger was the tick at the front of the cell. Without it, Outlook hoses numbers on a phone number import.
I only used C1 as an example. It presumes the phone number was in C1. If your phone number field is column F, then you would use F1. Once you type it in, you just drag the formula down the lenght of the spreadsheet. Without an example spreadsheet it’s hard to explain.
That’s a good suggestion, Ted. But for a few issues.
1: The formula you used, =TEXT(C1,0) pulls the contents of the C1 cell. Wouldn’t work unless I was looking at that column all the time. And more specifically, I needed to run this macro seven times (two spreadsheets, five phone numbers, two zip codes). I’m not terribly familiar with Excel, but I’m sure it could use some sort of offset so that it can draw the value next to whatever cell you’re in. Otherwise I’d have to change the value for each column, adding precious seconds to the total time. Figuring out the offset code would have added even more.
2: For some reason, the Outlook import seems to like to have the tick (‘) in front of the field when it imports. Otherwise the results are… odd. This is generally not an issue when you’re talking about a text field, such as “First Name”. But for fields that contain numbers, such as Phone Number and Postal Code, and even for text values in those fields, such as a Canadian Postal Code, it likes the tick. I can’t explain it.
3: Fomatting the cells as text changes the display of the cell, not the contents. It didn’t help. I tried that first. 🙂
I would have..
– Inserted a blank column next to my phone number field
– Typed “=TEXT(C1,0)”
– Copied the function down the whole spreadsheet
– Click at the top of the column to select it.
– Right-Click, copy
– Right-Click, Paste Special, values
– Delete original column
It takes about 30 seconds
Alternatively, you might have been able to select the phone number column and then right-click, format, text. That might have worked too.