# 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!

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?

Hello,

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:

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.