Re: Can't get my custom cell format to stick

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

You may want to paste the formula you used, but I bet you left the new value as
text.

If the original formula looked like:
=mid(a1,1,2)&mid(a1,3,6)&mid(a1,7,19)
This will return text.

You could modify the formula so that it looks like:
=--(mid(a1,1,2)&mid(a1,3,6)&mid(a1,7,19))

The -- coerces the text number to a real number.

If you don't want to go back to the original data, you can try this:

Select an empty cell
edit|copy
Select the range to fix
Edit|copy|Paste special|check Add and Values

You may have to reapply the number formatting you want.

CFOconsulting wrote:

I am trying to create a spread*** that, once saved as a .txt file, will be
compatible with our accounting software (Timberline) and will enable us to
IMPORT job cost budgets rather than having to re-key.

Using the text wizard, I exported from Timberline the master cost code list.
The ID field is structured as 00-00000000, including punctuation. Using
CONCATENATE and MID functions, I was able to remove the dash from the ID
field (a requirement in the template).

I created a custom format (00-00000000) so that the ID would be easier on
the eyes for the user and applied it to the entire ID column. Unfortunately,
I cannot get any of the cells in the ID column (using Paste->Special->Values)
to appear as 00-00000000 even though data in the formula bar is being
properly presented as 0000000000.

The only way I can get the cell to appear properly is if I keystoke over the
values I "pasted special". With 550 codes to fix, rekeying is not my first
choice.

Am I doing something out of sequence, or omitting a step prehaps?
--
Thanks for your help,

dlewanda

--

Dave Peterson
.

Quantcast