Macro to insert "+" character before a number

[schtumpster]

Hi,

I have searched this and other forums for a macro which will insert a "+" (international dialling prefix) character before a phone number in an Excel field with no success. I have tried the "record macro" function without success. Any ideas?

Thanks.

[Palmetto]

Please Login or Register  to view this content.

[mudraker]

If Excel considers the entry in the cell is a number it will remove the + symbol

[DonkeyOte]

Another alternative might be to apply a Custom Format to those cells containing the numbers, ie: +#;;;+@

The above would not physically alter the values but would display and print the + operator.

[Palmetto]

If Excel considers the entry in the cell is a number it will remove the + symbol

I am forcing the entry to text by prepending the "+" character with the single quote - so it will add the character but convert the cell content to text.

[T.J. Lanza]

I have searched this and other forums for a macro which will insert a "+" (international dialling prefix) character before a phone number in an Excel field with no success. I have tried the "record macro" function without success. Any ideas?

Not to step on anybody's toes, but what's the goal here? Why do you want the + symbol to show up? Are all the items in the range international phone numbers?

If it's just a display issue, DonkeyOte has the right answer. Special number formats are your friend. I use them quite extensively when I want to mix text into a cell with numbers, but still be able to use the number as part of calculations.

If you actually need to do something else with the number, it can get more complicated.

[schtumpster]

Thanks for the replies so far.

Donkey,

It's not just a display requirement. I'd like the character "+" to be the first character of the field, followed by the phone number. I don't think it matters if it is text or a number, although maybe it should be just text as Mudraker states.

Timothy,

I need the "+" as I am going to export the data into Outlook, then for synchronisation with a Nokia phone. The end result is that all numbers in my phone are preceeded by a "+"<county code> so that I can dial them from anywhere internationally. I presently have the field as needed (including country codes), now just need the "+". There are a few hundred numbers so trying to avoid doing it manually.

Palmetto,

Thanks for the code, but it inserts a "+" infront of cell A1 only. I need it to run on any selected cell(s). Is this an easy mod?

Cheers!

[shg]

I need it to run on any selected cell(s).

Please Login or Register  to view this content.

[schtumpster]

thanks SHG, this works fine. I will check that the export to Outlook and Sync with Nokia phone is successful and post again later.

[martindwilson]

can you export a csv to outlook? beacause when its in csv format the custom format donkeyote suggested is maintained in the csv
see attached text a renamed csv which was formatted to add +

[schtumpster]

Thanks all - worked fine through the import to Outlook and Sync with Nokia.

Martin - I had a little bit of other editing to do so excel worked better for me than csv, but thanks for the info.

Cheers!