shekhar_pc
Board Regular
- Joined
- Jan 29, 2006
- Messages
- 185
This is the RAW sheet
This is how I have to convert it
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?
Book1 | ||||||
---|---|---|---|---|---|---|
B | C | D | E | |||
3 | REP 19:40 999/888 777D XXX-YYY-ZZZ WWW-VVV 1940 0130 AA-666-ABCDEF ABCDEFGHI BB-888-ABCDEF ABCDEFGHI ZZ-555-ABCDEF ABCDEFGHI | REP 17:30 666/555 444 MAM-RAM-JAI JAI-MAM 1730 2315 CC-999-ABCDEF ABCDEFGHI DD-555-ABCDEF ABCDEFGHI EE-111-ABCDEF ABCDEFGHI | REP 00:00 222 AAA-BBB 0000 0155 YY-999-ABCDEF ABCDEFGHI | REP 05:00 333 RAM-MAM 0500 0700 SS-777-ABCDEF ABCDEFGHI | ||
4 | L\A - MAM 0000 2359 | REP 17:15 222/333 444 MAM-ZOM-BRU BRU-MAM 1715 2325 FF-555-ABCDEF ABCDEFGHI BB-999-ABCDEF ABCDEFGHI CC-444-ABCDEF ABCDEFGHI | ABCDE FGHI | REP 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 | ||||||
---|---|---|---|---|---|---|
B | C | D | E | |||
3 | 999/888 777D | 666/555 444 | 222 | 333 | ||
4 | L\A | 222/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?