I need an excel macro that copies a portion of text from one column to a new column.?

Question

I have a spreadsheet where the number of rows can change. Column D has a bunch of text in which I need to copy numbers from that column to a new column. The number is always so many spaces over, but are not the same numbers so I can't do a search. For Example text in D2 = phone number 555-555-5551...additional text. I need just the numbers copied to C2. Text in D3 = phone number 555-555-5553...additional text, again I need just the numbers copied to C3.

Answer 1

if the phone number is always the same length, then you can do a right / left function in excel

example

enter this function in c3 "=LEFT(d3,12)"

if you want to remove the numbers then do this

Remove a specified number of characters from the right or left side of text

To do this task, use the LEN, LEFT, and RIGHT functions.

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

Create a blank workbook or worksheet.

Select the example in the Help topic.

Note  Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.

In the worksheet, select cell A1, and press CTRL+V.

To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

  

1

2

3

A

Data

Vitamin A

Vitamin B1

Formula Description (Result)

=LEFT(A2, LEN(A2)-2) Removes last two characters from contents of A2 (Vitamin)

=RIGHT(A3, LEN(A3)-8) Removes first 8 characters from A3 (B1)



Function details

LEN, LENB

LEFT, LEFTB

RIGHT, RIGHTB

Top of Page

Answer 2

You requested an Excel macro.  Here is the macro that will review all entries in Column D, no matter how many there are, and copy only the numbers to the corresponding cell in Column C.

Open your workbook

Copy this macro to the clipboard:

Sub Strip_Numbers()

Dim rng As Range

Dim i

Dim NumbsOnly

Set rng = Range("D1:" & Range("D65536").End(xlUp).Address(0, 0))

For Each cell In rng

cell.Select

NumbsOnly = ""

For i = 1 To Len(ActiveCell)

If IsNumeric(Mid(ActiveCell, i, 1)) Then

NumbsOnly = NumbsOnly & Mid(ActiveCell, i, 1)

End If

Next

Next

ActiveCell.Offset(0, -1).Value = NumbsOnly

End Sub

Next, press ALT + F11

Insert  >  Module

Paste the macro into the Module space to the right.

Close back to Excel.

Go to Tools  >  Macro  >  Macros

Highlight this macro, if it is not already highlighted.

Click 'Options'

Select a letter to be used as a keyboard shortcut.

Close back to Excel.

Press CTRL  +  your shortcut letter to run the macro.

Answer 3

If the text always start with "phone number (number)..." and the number is always the same length, you can do this is the C2:

=MID(D2,14,12)