Text To Columns and Calculation Macro

Hello Guys,

I am working on a class project and I think I've confused myself. I was hoping you could help me.

I need a macro that splits alphabets after the file number and calculates the number of files generated.

All file numbers are start with "QLHR" followed by 6 numbers (QLHR000000)
Eg: QLHR049217

However, if a file is replicated the system adds "A", if its replicated again it goes to "B", if its replicated again it goes to "C" and so on.

File Count:
No Number: 1
A: 2
B: 3
C: 4
D: 5
E: 6

and so on until Z

I have attached sample of what I am working on. (it has a macro in there, It's gone horribly wrong).
tab 1 is the data
tab 2 is the output required.


Please help me.
Thanks for reading
Tai

Hi daffodil11,

Thank you so much for your help. sorry for late reply I was having log in problems.

The code works great! Thanks

I am trying to Split the letter "K" from column C (cells named "EstimateUnits") to cells E and letter "k" from column D (cells named "ActualUnits") to cell F. Below is the code I have made. However, it keeps giving me an error.

Can you help please?
**File attached**
FileCount.xlsm

Please Login or Register  to view this content.

Thanks
Tai