SOLVED

An addition without changing the existing information

%3CLINGO-SUB%20id%3D%22lingo-sub-1585363%22%20slang%3D%22en-US%22%3EAn%20addition%20without%20changing%20the%20existing%20information%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1585363%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20add%20a%20%22%2B1%22%20to%20phone%20numbers%20in%20my%20spreadsheet.%20When%20I%20try%20to%20add%20the%20%2B%2C%20Excel%20thinks%20I%20am%20trying%20to%20do%20a%20math%20equation.%20Is%20there%20a%20way%20to%20turn%20this%20off%3F%3C%2FP%3E%3CP%3EAlso%2C%20when%20I%20receive%20my%20spreadsheets%2C%20the%20phone%20numbers%20are%20automatically%20in%20the%20sheet.%20Yet%2C%20where%20I%20need%20to%20upload%20them%20requires%20the%20%22%2B1%22%20in%20order%20to%20use%20them%20effectively.%20Is%20there%20a%20way%20for%20me%20to%20do%20this%20in%20bulk%20without%20it%20changing%20the%20existing%20data.%20When%20I%20tried%20dragging%20it%20down%2C%20it%20would%20take%20that%20same%20telephone%20number%20and%20replace%20all%20of%20the%20other%20ones%20below%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1585363%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1585397%22%20slang%3D%22en-US%22%3ERe%3A%20An%20addition%20without%20changing%20the%20existing%20information%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1585397%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F757746%22%20target%3D%22_blank%22%3E%40mflurry%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20phone%20numbers%20are%20in%20A2%20and%20down.%3C%2FP%3E%0A%3CP%3EIf%20necessary%2C%20insert%20an%20empty%20column%20in%20column%20B.%3C%2FP%3E%0A%3CP%3EEnter%20the%20following%20formula%20in%20B2%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3D%22%2B1%22%26amp%3BA2%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3D%22%2B1-%22%26amp%3BA2%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E(whichever%20produces%20the%20desired%20result).%3C%2FP%3E%0A%3CP%3EThen%20fill%20down.%3C%2FP%3E%0A%3CP%3EIf%20you%20wish%2C%20you%20can%20remove%20the%20formulas%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3ESelect%20the%20range%20with%20the%20formulas.%3C%2FLI%3E%0A%3CLI%3ECopy%20it.%3C%2FLI%3E%0A%3CLI%3EClick%20the%20lower%20half%20of%20the%20Paste%20button.%3C%2FLI%3E%0A%3CLI%3ESelect%20Values.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3EYou%20can%20now%20delete%20the%20original%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1585413%22%20slang%3D%22en-US%22%3ERe%3A%20An%20addition%20without%20changing%20the%20existing%20information%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1585413%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat's%20it.%20Thank%20you%20so%20much!%20You've%20saved%20me%20hours%20of%20time!%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello!

 

I am trying to add a "+1" to phone numbers in my spreadsheet. When I try to add the +, Excel thinks I am trying to do a math equation. Is there a way to turn this off?

Also, when I receive my spreadsheets, the phone numbers are automatically in the sheet. Yet, where I need to upload them requires the "+1" in order to use them effectively. Is there a way for me to do this in bulk without it changing the existing data. When I tried dragging it down, it would take that same telephone number and replace all of the other ones below it. 

 

Thank you!

2 Replies
Best Response confirmed by mflurry (New Contributor)
Solution

@mflurry 

Let's say the phone numbers are in A2 and down.

If necessary, insert an empty column in column B.

Enter the following formula in B2:

 

="+1"&A2

 

or

 

="+1-"&A2

 

(whichever produces the desired result).

Then fill down.

If you wish, you can remove the formulas:

  • Select the range with the formulas.
  • Copy it.
  • Click the lower half of the Paste button.
  • Select Values.

You can now delete the original column.

@Hans Vogelaar 

 

That's it. Thank you so much! You've saved me hours of time!