Getting sensible data from data garbage

shekhar_pc

Board Regular
Joined
Jan 29, 2006
Messages
185
This is the RAW sheet
Book1
BCDE
3REP 19:40 999/888 777D XXX-YYY-ZZZ WWW-VVV 1940 0130 AA-666-ABCDEF ABCDEFGHI BB-888-ABCDEF ABCDEFGHI ZZ-555-ABCDEF ABCDEFGHIREP 17:30 666/555 444 MAM-RAM-JAI JAI-MAM 1730 2315 CC-999-ABCDEF ABCDEFGHI DD-555-ABCDEF ABCDEFGHI EE-111-ABCDEF ABCDEFGHIREP 00:00 222 AAA-BBB 0000 0155 YY-999-ABCDEF ABCDEFGHIREP 05:00 333 RAM-MAM 0500 0700 SS-777-ABCDEF ABCDEFGHI
4L\A - MAM 0000 2359REP 17:15 222/333 444 MAM-ZOM-BRU BRU-MAM 1715 2325 FF-555-ABCDEF ABCDEFGHI BB-999-ABCDEF ABCDEFGHI CC-444-ABCDEF ABCDEFGHIABCDE FGHIREP 06:35 111/222 333D MAM-ZZZ-MAA MAA-MAM 0635 1210 RR-333-ABCDEF ABCDEFGHI WW-999-ABCDEF ABCDEFGHI KK-333-ABCDEF ABCDEFGHI
Sheet1


This is how I have to convert it
Book1
BCDE
3999/888 777D666/555 444222333
4L\A222/333 444-111/222 333D
Sheet2


I want find the word "REP" first. After that word is found, you will have time in 24 hours format. I want to retrive the next numbers.

These numbers will always be three digits, in some cases four digit with the last letter "D" (it will always be the same letter "D"

something like 111 or 111/222 or 111 222 or 111/222 333 or 111/222D or 111 222D or 111/222 333D

My actual raw data is in the range B3:AI500. I want to scan each cell within this range and extract the numbers I want and put it in the same range in sheet2

Can somebody help please?
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Hi
Paste the following codes in the macro window ( alt F11)

Code:
Sub kkk()
For a = 2 To 35
For b = 3 To 500
c = Cells(a, b)
d = InStr(c, "REP")
e = InStr(d + 10, c, " ")
Worksheets("sheet2").Cells(a, b) = Mid(c, d + 10, 12)
Next b
Next a
End Sub
Run the macro. It will recognize "REP" and extracts 12 characters from where time ends and copies it to sheet 2. Let us refine it in the next round based on the output you get.
Ravi
 
Upvote 0
correct output except one change, if any cell starts with L\A, the new cell value should be "L\A" instead of blank. If there is no "REP" or "L\A" then it can be blank.

Now the second part. As I said in my first post, I want to retrive the three digit numbers and in some cases four digit only if the letter "D" is found next to the three digits.

something like 111 or 111/222 or 111 222 or 111/222 333 or 111/222D or 111 222D or 111/222 333D

Thanks for your help
 
Upvote 0
Hi
try the following modification
Code:
Sub kkk()
For a = 2 To 35
For b = 3 To 500
c = Cells(a, b)
d = InStr(c, "REP")
If d = 0 Or Left(c, 3) = "L/A" Then
Worksheets("sheet2").Cells(a, b) = ""
Else
e = InStr(d + 10, c, " ")
f = Mid(c, d + 10, 12)
If Right(f, 1) = "D" Then
g = f
Else
g = Left(f, InStr(f, " "))
Worksheets("sheet2").Cells(a, b) = g
End If
Next b
Next a
End If
End Sub
Blanking upon L/A and absence of rep are OK. but third condition does not seem to be consistent from your example. even without D, you have 11 letters (as in 111/222 333).
Ravi
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top