How do I?

[Tarqs]

I have a report which contains a list of other report names in column A. I need to be able to paste part of this name into Column D. The report name will always be in this format DPS11/895/Ret/KC. I need to be able to paste the 895 to Column D.

Help is greatfully received.

Tarqs

 

[doofusboy]

First, click on the cell containing the info you want to copy. Then in the formula bar [appears as a textbox above your worksheet and under the menus] hightlight just that portion that you want to copy, hit Ctrl and 'C' keys simultaneously to copy. Then click on cell in column D and hit Ctrl and 'V' keys simultaneously to paste.

 

[Tarqs]

Apologies I wasn't clear. I need to do this on mass. I have approximately 200 rows of information and report changes on a weekly basis.

 

[kidwispa]

If your data starts in cell A1 use the formula:

=MID(A1,7,3)

EDIT - To give a bit more information, A1 refers to the cell, 7 is the number of characters from the left hand side and 3 is the number of characters you wish to show

 

[Tarqs]

Perfect thank you !

 

[hiker95]

Tarqs,


Sample raw data for lasgt week, before the macro:

Excel Workbook
	A	B	C	D
1	Report Name	Title B	Title C	Report #
2	DPS11/892/Ret/KC	2	2
3	DPS11/893/Ret/KC	3	3
4	DPS11/894/Ret/KC	4	4
5
6
7
8
Sheet1

After the macro:

Excel Workbook
	A	B	C	D
1	Report Name	Title B	Title C	Report #
2	DPS11/892/Ret/KC	2	2	892
3	DPS11/893/Ret/KC	3	3	893
4	DPS11/894/Ret/KC	4	4	894
5
6
7
8
Sheet1

Then you add some new data:

Excel Workbook
	A	B	C	D
1	Report Name	Title B	Title C	Report #
2	DPS11/892/Ret/KC	2	2	892
3	DPS11/893/Ret/KC	3	3	893
4	DPS11/894/Ret/KC	4	4	894
5	DPS11/895/Ret/KC	5	5
6	DPS11/8951/Ret/KC	6	7
7	DPS11/895123/Ret/KC	6	7
8
Sheet1

And, run the macro again:

Excel Workbook
	A	B	C	D
1	Report Name	Title B	Title C	Report #
2	DPS11/892/Ret/KC	2	2	892
3	DPS11/893/Ret/KC	3	3	893
4	DPS11/894/Ret/KC	4	4	894
5	DPS11/895/Ret/KC	5	5	895
6	DPS11/8951/Ret/KC	6	7	8951
7	DPS11/895123/Ret/KC	6	7	895123
8
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ExtractReportNbr()
' hiker95, 04/11/2011
' http://www.mrexcel.com/forum/showthread.php?t=542626
Dim LR As Long, FR As Long, a As Long
Dim Sp
Application.ScreenUpdating = False
LR = Cells(Rows.Count, "A").End(xlUp).Row
FR = Range("D" & Rows.Count).End(xlUp).Offset(1).Row
For a = FR To LR Step 1
  Sp = Split(Cells(a, 1), "/")
  Cells(a, "D") = Sp(1)
Next a
Application.ScreenUpdating = True
End Sub

Then run the ExtractReportNbr macro.

 

[Tarqs]

Hiker 95,

That is very slick! works perfectly.

Thank you very much.

Tarqs