Ask Your Question

Telephone numbers

asked 2019-10-06 15:16:57 +0100

mohawk38 gravatar image

I have a long column of telephone numbers in the form 1-123-555-1234, and I would like to remove the leading number 1, all in one operation. Is there a way to do this? Thanks.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2019-10-06 15:41:06 +0100

ajlittoz gravatar image

I hope your telephone numbers are considered as text bt Calc (text is left justified while numbers are right justified by default). If they are numbers on their own (i.e. the dashes have been added by cell formatting), there's little you can do, apart subtract 10,000,000 from the numbers.

With text:

  • Edit>Find & Replace, check Regular expressions
  • in Find, enter ^1-(.*)
  • in Replace, enter $1

Press some Find Next and Replace to see if it works right, then Replace All.

The regular expression tries to find a cell containing text starting (^) with 1- and captures the rest (.*). Since there are parentheses around the "rest", this is known as the first capture group. The capture group $1 is the replacement string.

To show the community your question has been answered, click the ✓ next to the correct answer, and "upvote" by clicking on the ^ arrow of any helpful answers. These are the mechanisms for communicating the quality of the Q&A on this site. Thanks!

edit flag offensive delete link more


Thank you for your reply. I converted the numbers to text and used you suggestion. It worked like a charm and saved me a lot of work. Cheers

mohawk38 gravatar imagemohawk38 ( 2019-10-06 18:31:34 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-10-06 15:16:57 +0100

Seen: 42 times

Last updated: Oct 06