VBA macro cutting off information

Good afternoon, I'm an RPA Analyst currently building a bot that automates a process for my AR team. In my automation, there are steps that execute macros and for the most part, they don't give me any trouble. But recently I've noticed that one of the simple ones is cutting off information that's vital to the completion of the process.

It starts when I have my "bot" download a payments report. The important thing to watch out for here is the last 4 digits of what is the confirmation number for each payment. They show up as expected in this report, and when I open the report up in Excel after downloading it. After the report is downloaded, its contents are copied and saved as a new tab in a different workbook and the macro is run on it. The part of the macro I'm having issues with is the formula that takes the last 4 digits of that confirmation number.

For a reason I can't figure out, the macro is only taking the last 2 digits. It used to take the last 4 digits without any problems, but now that isn't the case. I've also noticed that the actual confirmation number is chopped at the end. In most cases, the last number is off and I can't figure out why that is. I could almost live with the chopped confirmation number, but those last 4 digits are something I absolutely need and I'm not sure why I'm getting the results I am.

Any suggestions are appreciated, I can provide more media if needed.

Thank you

I got them stored as text, whatever you wrote kept giving syntax errors but w/e. But I'm still having the problem of the actual number being altered. Like when I review the whole confirmation number I'm seeing the last digit not showing up.