Text to columns Fixed With merging two columns with a decimal

[ItalianPlatinum]

Looking for the easiest way to combine two columns with a decimal. I have a file that has prices on it. But it comes to me with the prices all together without the decimal. Below is an example (1,000+ records) and it is a TXT file so when I convert it over to excel that is all in 1 cell. So I can convert it to the correct columns I desire but the prices just end up on their own columns (left of decimal in one column right of decimal in the other). I wasn't sure if can be handled via text to columns or do I really need to find a way to merge it somehow?

ST1AAAAAAA20240410XXXXXX000000002452530000END 00000000248991000020240409ABCD

4/10 Price should be 24.5253
4/09 Price should be 24.8991

Left side of space gap: There will always be 4 zeroes before the "END"
Right side of space gap: There will always be 4 zeroes before the prior day's date

    Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 2), Array(2, 2), Array(11, 1), Array(19, 1), Array(25, 1), _
        Array(35, 1), Array(39, 1), Array(43, 1), Array(56, 1), Array(78, 1), Array(82, 1), Array( _
        86, 1), Array(94, 2)), TrailingMinusNumbers:=True
    .Columns("A:L").AutoFit

Thanks in advance for looking at this.

 

[SanjayGMusafir]

Dividing the two columns with 10,000 is the easiest solution

Here is the information (I have edited it to your custom need)

Use Paste Special and Divide​

If the steps above didn't work, you can use this method, which can be used if you're trying to convert more than one column of text.

1. Select a blank cell that doesn't have this problem, type the number 10000 into it, and then press Enter.
2. Press Ctrl + C to copy the cell.
3. Select the cells that have numbers stored as text.
4. On the Home tab, select Paste > Paste Special.
5. Select Divide, and then click OK. Excel multiplies each cell by 1, and in doing so, converts the text to numbers.

from the link below -

Convert numbers stored as text to numbers in Excel - Microsoft Support

When numbers are actually stored in cells as text, problems with calculations or sorting can occur. Read this article to learn how to fix this problem.

support.microsoft.com

 

[ItalianPlatinum]

I will give that a try. Thank you. I have found a VBA for the paste special divide.