I have used Excel for a long time. I rarely come across an issue that I can't figure out, but this has me stumped. I have looked extensively for answers on the web to no avail. I have a database program that I need to Export data from into Excel 365. Some of the data, but not all, does not align right properly, and some of the data won't align left properly.
Ex: (The second number is the problem data)
AF4681
AF61270
I have tried to massage the data into another cell using the TRIM function, and it looks exactly the same, that is, it looks like it is aligned right with a 1.5 space indent (impossible, I know).
When I look at the text in the formula bar, the data appears with one very long space before the text, almost like a tab was inserted.
______AF61270
If I remove the very long single "space" by backspacing 1 time or deleting 1 time, formatting becomes normal.
AF61270
Some of the other data in the same column has the same issue, but some of it comes across with a reversed issue. That is, it will justify right completely, but it will not justify left by what looks like a 0.5 space indent (an odd half indention).
Ex (The first number is the problem number)
AF4681
AF61270
When I look at the text string in the formula line, there is a very small space before the text string
_AF4681
One other observation is that if I copy the data from the formula bar and past it into a cell,
Normally, I want to copy the exported data from the database to a blank worksheet. On another tab, I have a formatted form (table) to extract the data from the raw data and put it into a specific format required by the company asking for the data. I really don't want to have to clean up the data, or really even touch it. I want to import it and the formatted tab is print ready with no intervention. This errant formatting is requiring me to clean up hundreds of errors. Find and replace doesn't work, because apparently the big space is something other than a valid search parameter. It would be a sub-optimal solution anyway because I really want to import and the just print the resulting table on the formatted form tab. The TRIM function does not recognize the long space as a space either or that would have been an acceptable solution. I have searched for formatting, conditional formatting, and the style says NORMAL. I have tried using the "copy format" brush to copy the format from cells that are working correctly. The only oddity is that this is the only column that has a text string composed of both alpha & numeric data. Changing the format to numeric does not fix anything.
I am tapped out of ideas, and my research is not giving me useful answers. Any ideas would be welcome.
