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.
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 )
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.
Use Flash Fill feature. In the adjacent column, enter an example on how you want your data to appear. Click Enter. Click Data - Flash Fill. Your data will auto-populate.
