Can't get my custom cell format to stick

[CFOconsulting]

I am trying to create a spreadsheet 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?

 

[Dave Peterson]

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]

Thanks for the prompt reply, Dave. Unfortunately, neither of your
suggestions worked.
1) when I tried editing: =MID(A2, 2, 11) as: =--(MID(A2, 2, 11)) I got
the following error message: #VALUE!
2) as noted in my original message, when I copy the cell with the MID
formula and Paste Special it (Values) to an empty cell, I get the same
results.

dlewanda

 

[Dave Peterson]

If the characters returned from the =mid() function are all numbers, then the --
stuff will work.

If you have text in any of those characters, then the -- stuff will return the
value error.

But if you're working with text, then changing the numberformat for the cell
won't change how that cell's value is displayed.

I guess I'm confused at what you're doing.

Thanks for the prompt reply, Dave. Unfortunately, neither of your
suggestions worked.
1) when I tried editing: =MID(A2, 2, 11) as: =--(MID(A2, 2, 11)) I got
the following error message: #VALUE!
2) as noted in my original message, when I copy the cell with the MID
formula and Paste Special it (Values) to an empty cell, I get the same
results.

dlewanda

 

[CFOconsulting]

I got it to work, Dave. Thanks for your patience with me.

As it turns out, I double checked the accounting software structure, and
realized that the ID field was in fact "alpha" rather than "numeric". So I
changed my test by using CONCATENATE to join the 00 with the 00000000 (which
were MID separately, in separate columns). Putting your "double minus sign"
in front of the CONCATENATE formula gave me the desired results, and the
custom format field now works!