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:
Column A and start in Row 1 (i.e. first cell is A1)Tel: exactly (4 characters)6000 (just as an example)Step 1 - Get the phone numbers in column B by a formula
=IF(LEFT(A1,4)="Tel:",A1,"") into cell B1
B1
B1:B6000 into the Name Box of the Formula Bar
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)
CTRL+C (or Edit -> Copy
CTRL+SHIFT+V and in category Seclection only select option Text (all other items are unticked)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
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.
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 
to the left of the answer, to finish the question.
