Numbers in cells not responding to Format Cells

I work with an accounting software primarily for schools, churches called Logos Management Software. It has General Ledger, A/P and Payroll modules, Reporting and more. Reports can be exported to Excel which I do regularly.

Today I imported an income and expense report from the Logos software. In the imported Excel spreadsheet the amounts/numbers in the cells are left justified with $ sign, commas and decimal at 2 places. Until today’s import, I have had no problem highlighting the amounts/numbers, right click, select Format Cells, select Number, select 2 decimal places, comma and the numbers would move to the right and drop the $ sign.

With the report I imported today, I highlighted the amounts/numbers, right click, select Format Cells, select Number, select 2 decimal places, select comma, clicked ok and nothing happens....the amounts/numbers remained as is. What is strange is when I select Number in the Format Cells dialog box, in the Sample box the $ sign remains. I can enter a number in another empty cell and can format that number successfully.

Thanks for any suggestions.

texasphil

Lots of times, there's extra characters in those cells that make the value
non-numeric.

If you retype one of the entries, does it work ok?

If you have way too many to do this manually, you may want to try David
McRitchie's routine to clean the data:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

texasphil wrote:
>
> I work with an accounting software primarily for schools, churches
> called Logos Management Software. It has General Ledger, A/P and
> Payroll modules, Reporting and more. Reports can be exported to Excel
> which I do regularly.
>
> Today I imported an income and expense report. In the imported Excel
> spreadsheet the amounts/numbers in the cells are left justified with $
> sign, commas and decimal at 2 places. Until today’s import, I have had
> no problem highlighting the amounts/numbers, right click, select Format
> Cells, select Number, select 2 decimal places, comma and the numbers
> would move to the right and drop the $ sign.
>
> With the report I imported today, I highlighted the amounts/numbers,
> right click, select Format Cells, select Number, select 2 decimal
> places, select comma, clicked ok and nothing happens....the
> amounts/numbers remained as is. What is strange is when I select
> Number in the Format Cells dialog box, in the Sample box the $ sign
> remains. I can enter a number in another empty cell and can format
> that number successfully.
>
> Thanks for any suggestions.
>
> texasphil
>
> --
> texasphil
> ------------------------------------------------------------------------
> texasphil's Profile: http://www.excelforum.com/member.php...o&userid=32615
> View this thread: http://www.excelforum.com/showthread...hreadid=524094

--

Dave Peterson

If the data is left aligned and you don't have any alignment set, the data
is text, here's one way that might help

copy an empty cell, select the import and do edit>paste special and select
add, now try to format the
data

--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"texasphil" <[email protected]> wrote
in message news:[email protected]...
>
> I work with an accounting software primarily for schools, churches
> called Logos Management Software. It has General Ledger, A/P and
> Payroll modules, Reporting and more. Reports can be exported to Excel
> which I do regularly.
>
> Today I imported an income and expense report. In the imported Excel
> spreadsheet the amounts/numbers in the cells are left justified with $
> sign, commas and decimal at 2 places. Until today's import, I have had
> no problem highlighting the amounts/numbers, right click, select Format
> Cells, select Number, select 2 decimal places, comma and the numbers
> would move to the right and drop the $ sign.
>
> With the report I imported today, I highlighted the amounts/numbers,
> right click, select Format Cells, select Number, select 2 decimal
> places, select comma, clicked ok and nothing happens....the
> amounts/numbers remained as is. What is strange is when I select
> Number in the Format Cells dialog box, in the Sample box the $ sign
> remains. I can enter a number in another empty cell and can format
> that number successfully.
>
> Thanks for any suggestions.
>
> texasphil
>
>
> --
> texasphil
> ------------------------------------------------------------------------
> texasphil's Profile:
> http://www.excelforum.com/member.php...o&userid=32615
> View this thread: http://www.excelforum.com/showthread...hreadid=524094
>

Another solution that may work is to put the number 1 in a cell, copy
it, then Paste-Special-Multiply to your data

texasphil wrote:
> I work with an accounting software primarily for schools, churches
> called Logos Management Software. It has General Ledger, A/P and
> Payroll modules, Reporting and more. Reports can be exported to Excel
> which I do regularly.
>
> Today I imported an income and expense report. In the imported Excel
> spreadsheet the amounts/numbers in the cells are left justified with $
> sign, commas and decimal at 2 places. Until today’s import, I have had
> no problem highlighting the amounts/numbers, right click, select Format
> Cells, select Number, select 2 decimal places, comma and the numbers
> would move to the right and drop the $ sign.
>
> With the report I imported today, I highlighted the amounts/numbers,
> right click, select Format Cells, select Number, select 2 decimal
> places, select comma, clicked ok and nothing happens....the
> amounts/numbers remained as is. What is strange is when I select
> Number in the Format Cells dialog box, in the Sample box the $ sign
> remains. I can enter a number in another empty cell and can format
> that number successfully.
>
> Thanks for any suggestions.
>
> texasphil
>
>