Multiple date from one cell
I need help on how can I extract multiple information from one cell given this data:
| Date | Filename | |
| 5/11/2008 | ER48113JBNML6-7-05JORGE_SANTOS | 1 |
| 5/11/2008 | SUPP48498CSTLA10-12-05NANCY_ANGELES | |
| 5/11/2008 | SXC49893DACSO12-7-05LUIS_SANTOS |
to other columns like this:
| Date | Type of Report | Doctor | Location | DOE/DOR | Patient Name |
| 5/11/2008 | ER | JBN | ML | 6-7-05 | JORGE_SANTOS |
| 5/11/2008 | SUPP | CST | LA | 10-12-05 | NANCY_ANGELES |
| 5/11/2008 | SXC | DAC | SO | 12-7-05 | LUIS_SANTOS |
| 1 |
| ER | JBN | ML | 6-7-05 | JORGE_SANTOS |
| 1 | 2 | 3 | 4 | 5 |
1. ER/SUPP/SXC - Different type of Report (First 2 to 4 letters in the filename)
2. JBN/CST/DAC - Different Doctors (next 3 letters after the Type of Report and numbers)
3. ML/LA/SO - Location (next 2 letters after the Doctor)
4. DOE/DOR - after the Location
5. Patient Name - Last characters in the file name.
Please help me.
| Attachment | Size |
|---|---|
| PatientNames.xls | 20 KB |
Patient Names
Done.
PatientNames.xls
Patient Name
Hello! Thanks for all your help!
VBA based Solution
PatientNames does not answer correctly to the issue, and contain some mistakes.
Indeed, Doctor column shows numbers!, Location column shows Doctors ....
This solution will not work if the number next to Type of Report has a number of digits different form 5.
I suggest a VBA based solution that splits data as requested.
As soon as sheet 2 is activated, data is read from sheet 1, processed then copied to sheet 2.
NB: Formulas in sheet 1 are not used and can be deleted.
Enjoy.
Imed Ghorbel.
find the first number
To work with Types of Report with more than 4 digits you should first locate the first number in de text.
If you have that you can use left(text; foundplace-1) to represent the Type of Report and work from there.
Re: find the first number
I developped a VBA solution on the file PatientNames posted above.
To download the solution please press on this link.
Dates and filenames should be entered in sheet 1.
Splitted data is processed in sheet 2, event code updates this sheet.
Enjoy.
Imed Ghorbel