how to automatically find and select phone numbers from other text

Hi, I’ve got a massive long list of establishments with addresses and phone numbers… I’d really like to have all the phone numbers in their own column…

Is there a way to have calc find and select only those rows with phone numbers so I can copy and paste them to the next column?

I’d do it by hand but there’s thousands of them!

image description

Not quite clear, what you want to achieve. Do you want the phone number row-by-row (loosing the information to which address it belongs, or just show once more to another column just right or left to the current column and therefore having three empty cells between phone numbers). Both result can be achieved, thus just tell what you would like it to look.

I want a way to automatically find and select ONLY the phone numbers

so that I can move them all over by one column. Like so:

All records are identical, occupy 3 lines, and jump 1?


may be there are smarter solutions but this is one, which might work for you (looks a bit lengthy, but can be done in a minute, if you are used to the procedure).

Following assumptions are made for the description:

  • The Name, Address and Phone Number rows ar in Column A and start in Row 1 (i.e. first cell is A1)
  • All Phone numbers start with string Tel: exactly (4 characters)
  • Last Row is, let’s say 6000 (just as an example)

Step 1 - Get the phone numbers in column B by a formula

  • Enter =IF(LEFT(A1,4)="Tel:",A1,"") into cell B1
  • Copy cell B1
  • Enter B1:B6000 into the Name Box of the Formula Bar
  • Copy the formula into the selected range of cells using CTRL+V (or Edit -> Paste)

Expected result: The phone numbers are now in column B (but evaluated by a formula) - see screenshot:

image description

Step 2 - Replace formula by the real text of the phone number in column B
(In Step 3 the phone numbers will be removed from column A, thus we neee to turn the numbers in column B to text, otherwise they will vanish if Step 3 has been executed)

  • Select column B (if not still selected after Step 1)
  • Copy to clipboard using CTRL+C (or Edit -> Copy
  • Paste Special using CTRL+SHIFT+V and in category Seclection only select option Text (all other items are unticked)
  • Click OK

Expected result: All phone numbers are now real values (text) in column B (no references to column A anymore)

Step 3 - Remove phone numbers in column A

  • Select column A

  • Go to Edit -> Find &Replace and set
    Find: ^Tel:.*
    Replace: - do not enter anything, keep it empty
    Other Options: Tick Current selection only and Regular expressions

  • Click button Replace All (may be after testing using Find All)

Screenshot 3.1 - Before Replace All

Screenshot 3.2 - After Replace All

Expected result: All phone numbers removed from column A

Hope that helps.

An alternative procedure for step 2: Data->Calculate->Formula to Value

@mikekaganski: Less error prone and faster - Thanks.


Following is an example, separating only the phone, and another separating, Name; Address and Phone.

Then Copy and Paste Special, Text and Sort to delete blank lines.

sample file

ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.

If the answer met your need, please click on the ball Descrição da imagem to the left of the answer, to finish the question.