Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

In column B I have contact number.
I want to add a country code to all of them.

For example, if the number is 123456, I want it to become 88123456.

Each number is in 1 row, and there are about a thousand of them, so I want an automatic way to do this that allows me to add the country code to each of them without a space.

share|improve this question
    
    
@Melpher, I'd argue this is an acceptable duplication as per item 3 of the guidelines - this is a "similar-but-not-quite-the-same duplicate" in my mind. –  DMA57361 Jan 6 '11 at 10:27
add comment

2 Answers

up vote 4 down vote accepted

In-place modification is not a simple as it could be, the fastest solution in my mind is a quick formula in a new column, then copy that column to values (removing the formula) and replace the old column.

You can use either of the following types of formula, they're equivalent and both can be extended to link as many items as you like:

= "88" & B1
= CONCATENATE( "88" , B1 )

What To Do

  • Create a new column created next to your data
  • Insert the above formula (modified as required) at the top of the new column
  • Fill down the formula to the bottom of the column.
  • Copy the entire column.
  • Paste Special as Values over the new column.
  • Double check the new data is correct.
  • Replace old data column with new, modified, column.
share|improve this answer
    
thanks alot, you saved my time, thanks again –  khalid Jan 6 '11 at 10:00
add comment

In Excel 2007 you can also use Conditional formatting:

Select the cells you want to change. In Conditional Formatting, choose "new rule"

Under "Rule type" choose "Format only cells that contain"

Under "Rule description" choose "Cell Value", "between", "0", "9999999" - you can of course tweak these values according to your requirements.

Click on [Format], and then on the Number tab, choose "Custom", and in the Type box, overtype "General" with ""88"0" (i.e. it should contain "88"0).

This is in some ways the same as the previous answer, but it will do it in place, without having to create a new column. Note however, the actual value in the cell will not include the "88", so if you subsequently want to copy or use the data in the cells, (besides plain vanilla copy & paste which will also replicate the conditional formatting) many methods will NOT include the "88" prefix.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.