Exporting Quicken data to Excel

nrvmoreira

Hello, The question is related to the following environment: Quicken Home, Business & Rental Property, Version: R50.16 - Build:27.150.16 running on Windows 11 Home. We must export data from a Quicken file to an Excel spreadsheet. The export function seems to work fine with no error messages. The problem arises when we try to format the exported data in Excel. Seems that some numerical fields in Quicken are exported as text. The fields in Quicken are in the American number format (xxx,xxx.00). We must format them as xxx.xxx,00 in Excel. We tried to analyze the exported Excel sheet. In a single column, we find some cells correctly exported as numbers, which Excel can correctly reformat. Other numerical fields are exported as text cells and therefore kept in the American format. We tried to open the exported Excel spreadsheet in various versions of Excel (Office 2019, 365 in Windows, Office 2021 in MacOS Ventura) with the same results. Any hint to solve this issue? Thanks in advance...

nrvmoreira

to help in the understanding: in the annexed figure column of the exported spreadsheet . The cells aligned to the left are text cells. Those aligned to the right are number cells….

Rocket J Squirrel

If you would like Quicken to always display numbers in the European format, you can do that by editing a configuration file.

Close Quicken. Open the file C:\ProgramData\Quicken\Config\QUICKEN.INI in a plain text editor. Look for the two lines

sDecimal=.
sThousand=,

Those lines may or may not be near each other. Switch the period and the comma, save the file, and reopen Quicken.

Sorry, I can't help with the number vs. text import issue.

QWinUser

@nrvmoreira on those numbers with the "US Format", I think you can use the Number Value formula to convert the US Format to the European Format.

=NUMBERVALUE(A1,".",",")

The first field is the cell where the number is located.

The second is the decimal separator. In US numbers the decimal separator is the period.

The third is the group separator. In US numbers it is a comma.

Each of the separators needs ot be enclosed in double quotes.

I think to use this formula effectively, maybe sort the numbers so the "US numbers" are grouped together to make it easier.

1. Sort of the column of numbers so the "Text" numbers are grouped (at the bottom or top of the list depending on how you sort).
2. Insert a column next to the column of numbers.
3. Apply the formula to the "text" or ("US" numbers)
4. For the European Numbers, just copy them over to the new column or use a plus sign (+) to bring them over by referencing them.
5. Once the new column is filled with the correctly formatted numbers, highlight the entire column and do a "Copy-Paste Special" to convert them from formulas to actual numbers.
6. To do a Copy-Paste Special" - once you have highlighted the whole column, do a Copy ("Ctrl-C") and then a "Paste Special" with the "Values" option selected.
7. You can then delete the original column or numbers, if you wish.

nrvmoreira

thank you Rocket J Squirrel and @QWinUser!!!!