Ommitting Unused Data on Cell

[deedii]

Hi Guys,

Its been a pleasure to be here again. I hope someone can lend me a help on my 2 problem.

Problem No. 1
Raw Data 1:
000000151985
000000116993
000000147470

Result Data 1:
0151985
0116993
0147470
0147471

From the example above I want to extract the 7 digits start from the last and remove the remaining digits for example 000000147470, I only need the last 7 digits to make it 0147470 as a result after extraction.

Problem No. 2
Raw Data 2:
000000009349003
000000140130001
000000067054003
000000114914006

Result Data 2:
0009349
0140130
0067054
0114914

Here I would like to extract the 7 digits after 3 digits from the last and remove the rest. For example 000000114914006, I want to remove 006 from the last 3 digits and 00000 from the beginning so I can only have 0114914 as the data extracted. So i want to remove the 3 last digits and the first 5 digits and extract only the middle 7 digits.

Thank you so much guys :) TIA

[JKwan]

Looking at the limited data set, this is the easiest
Problem 1:
=MID(A1,6,7)
Problem 2:
=MID(A2,6,7)
You can play with the parameters of the MID function and get what you want

[Teeroy]

Hi deedii,

JKwan's responses are correct but depend on consistent input data where your output definitions don't.
Some alternate formulae that I think better match your descriptions are:
Problem 1:
=RIGHT(A1,7)

Problem 2:
=MID(A1,LEN(A1)-9,7)

[deedii]

Hi JKwan and Teeroy both are doing great, thanks so much for the hand the formula is doing great however I would like to make it as macro that will run thru the column. Example my data are in A column I would like to make the B column as the extracted data in column A. Would that be possible since I think it will still eat time if I paste of drag the formula individually?

Can this be simplify?

Range("B2").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],7)"
Selection.AutoFill Destination:=Range("B2:B100"), Type:=xlFillDefault
Range("B2:B100").Select

ActiveCell.FormulaR1C1 = "=MID(RC[-1],LEN(RC[-1])-9,7)"
Selection.AutoFill Destination:=Range("D2:D100"), Type:=xlFillDefault
Range("D2:D100").Select

[Teeroy]

Yes it can (shown below) but the easiest way for this example is to just type in the formula and fill down manually. Do you really need the VBA approach?

With Range("B2")
.FormulaR1C1 = "=RIGHT(RC[-1],7)"
.AutoFill Destination:=Range("B2:B100"), Type:=xlFillDefault
End With
With Range("D2")
.FormulaR1C1 = "=MID(RC[-1],LEN(RC[-1])-9,7)"
.AutoFill Destination:=Range("D2:D10"), Type:=xlFillDefault
End With

[snb]

or:


sub M_snb()
[A1:A2000]=[if(A1:A2000="","",mid(A1:A2000,6,7))]
End Sub

[deedii]

Thanks so much for all the support guys :)
I finished the work automatically.
More power to VBAexpress. :thumb