"Finalizing" format change to text
 

Quick Version:
I've changed the format on a column of numbers, text and number-text
combinations to text format in order to make it match another file I'm
comparing with. The comparison is not finding ANY matches right away. I can
go back and double click in a cell (as if to edit it) and then press the
enter key and VIOLA! the match appears. It is almost as if I'm having to
finalize EACH INDIVIDUAL cell to make it work, which will not be effiecient,
as I've got 8,000 cells to click on.

QUESTION:
Is there a way to "finalize" this format change globally without having to
click on each cell?

Details:
We receive a LARGE price sheet (DOC A) from our vendor periodically. The
part number column is formatted as text. There are some alphanumeric values
in the column, but the majority are in this format: 12345.60.

I export a CSV document from my product database with the current pricing
(DOC B) and want to compare this document with the one from our vendor, among
others also in text format. When I open this CSV document, I format the part
number column to text to match the others.

The formula that I use on DOC A (Vendor sheet) looks at DOC B (our current
pricing) for the part number. If there is a match, then it dispays the
retail price in this column where my formula is. If there is not a match, it
dispays "Add To Site".

My formula is:
=IF(A376="","",IF(ISNA(MATCH(A376,Source!A:A,0))," Add To
Site",VLOOKUP(A376,Source!A:B,2,0)))

90% of the columns show "Add to Site" even though we have 90% of the items
on our site. I can go to the sheet that I manually formatted to text, double
click in one of the cells containing the part number, press my enter key and
then the match shows up on our vendor sheet instantly.

"Finalizing" format change to text
 

I'm not really following you here but one possibility is that you have your
spreadsheets set to manual, as oopposed to automatic, calculation.

Go to Tools--Options--Calculations and set it to automatic.

Dave
--
Brevity is the soul of wit.


"Nancy" wrote:

Quick Version:
I've changed the format on a column of numbers, text and number-text
combinations to text format in order to make it match another file I'm
comparing with. The comparison is not finding ANY matches right away. I can
go back and double click in a cell (as if to edit it) and then press the
enter key and VIOLA! the match appears. It is almost as if I'm having to
finalize EACH INDIVIDUAL cell to make it work, which will not be effiecient,
as I've got 8,000 cells to click on.

QUESTION:
Is there a way to "finalize" this format change globally without having to
click on each cell?

Details:
We receive a LARGE price sheet (DOC A) from our vendor periodically. The
part number column is formatted as text. There are some alphanumeric values
in the column, but the majority are in this format: 12345.60.

I export a CSV document from my product database with the current pricing
(DOC B) and want to compare this document with the one from our vendor, among
others also in text format. When I open this CSV document, I format the part
number column to text to match the others.

The formula that I use on DOC A (Vendor sheet) looks at DOC B (our current
pricing) for the part number. If there is a match, then it dispays the
retail price in this column where my formula is. If there is not a match, it
dispays "Add To Site".

My formula is:
=IF(A376="","",IF(ISNA(MATCH(A376,Source!A:A,0))," Add To
Site",VLOOKUP(A376,Source!A:B,2,0)))

90% of the columns show "Add to Site" even though we have 90% of the items
on our site. I can go to the sheet that I manually formatted to text, double
click in one of the cells containing the part number, press my enter key and
then the match shows up on our vendor sheet instantly.

"Finalizing" format change to text
 

Well, with a Text format, it can cause chaos like that. you can always create
a helper column to the side and have the cells =1* your original text
formatted cell amount. This will force-format to number in the helper.
Then, you can do your lookups from the new column...

"Nancy" wrote:

Quick Version:
I've changed the format on a column of numbers, text and number-text
combinations to text format in order to make it match another file I'm
comparing with. The comparison is not finding ANY matches right away. I can
go back and double click in a cell (as if to edit it) and then press the
enter key and VIOLA! the match appears. It is almost as if I'm having to
finalize EACH INDIVIDUAL cell to make it work, which will not be effiecient,
as I've got 8,000 cells to click on.

QUESTION:
Is there a way to "finalize" this format change globally without having to
click on each cell?

Details:
We receive a LARGE price sheet (DOC A) from our vendor periodically. The
part number column is formatted as text. There are some alphanumeric values
in the column, but the majority are in this format: 12345.60.

I export a CSV document from my product database with the current pricing
(DOC B) and want to compare this document with the one from our vendor, among
others also in text format. When I open this CSV document, I format the part
number column to text to match the others.

The formula that I use on DOC A (Vendor sheet) looks at DOC B (our current
pricing) for the part number. If there is a match, then it dispays the
retail price in this column where my formula is. If there is not a match, it
dispays "Add To Site".

My formula is:
=IF(A376="","",IF(ISNA(MATCH(A376,Source!A:A,0))," Add To
Site",VLOOKUP(A376,Source!A:B,2,0)))

90% of the columns show "Add to Site" even though we have 90% of the items
on our site. I can go to the sheet that I manually formatted to text, double
click in one of the cells containing the part number, press my enter key and
then the match shows up on our vendor sheet instantly.

"Finalizing" format change to text
 

I looked at that and I'm already on automatic..

"Dave F" wrote:

I'm not really following you here but one possibility is that you have your
spreadsheets set to manual, as oopposed to automatic, calculation.

Go to Tools--Options--Calculations and set it to automatic.

Dave
--
Brevity is the soul of wit.

"Finalizing" format change to text
 

When I first did this, I just double-clicked on my CSV file to open it in
Excel. Your post gave me the idea to take it back off of my comparison and
actually do the import external data and specify that the column should be
text there instead of trying to convert it to text after it's already placed
in my file.



"Sean Timmons" wrote:

Well, with a Text format, it can cause chaos like that. you can always create
a helper column to the side and have the cells =1* your original text
formatted cell amount. This will force-format to number in the helper.
Then, you can do your lookups from the new column...

"Nancy" wrote:

Quick Version:
I've changed the format on a column of numbers, text and number-text
combinations to text format in order to make it match another file I'm
comparing with. The comparison is not finding ANY matches right away. I can
go back and double click in a cell (as if to edit it) and then press the
enter key and VIOLA! the match appears. It is almost as if I'm having to
finalize EACH INDIVIDUAL cell to make it work, which will not be effiecient,
as I've got 8,000 cells to click on.

QUESTION:
Is there a way to "finalize" this format change globally without having to
click on each cell?

Details:
We receive a LARGE price sheet (DOC A) from our vendor periodically. The
part number column is formatted as text. There are some alphanumeric values
in the column, but the majority are in this format: 12345.60.

I export a CSV document from my product database with the current pricing
(DOC B) and want to compare this document with the one from our vendor, among
others also in text format. When I open this CSV document, I format the part
number column to text to match the others.

The formula that I use on DOC A (Vendor sheet) looks at DOC B (our current
pricing) for the part number. If there is a match, then it dispays the
retail price in this column where my formula is. If there is not a match, it
dispays "Add To Site".

My formula is:
=IF(A376="","",IF(ISNA(MATCH(A376,Source!A:A,0))," Add To
Site",VLOOKUP(A376,Source!A:B,2,0)))

90% of the columns show "Add to Site" even though we have 90% of the items
on our site. I can go to the sheet that I manually formatted to text, double
click in one of the cells containing the part number, press my enter key and
then the match shows up on our vendor sheet instantly.

"Finalizing" format change to text
 

Nancy

Format all to Genral then either of two methods.

1. Copy an unused cell.

Select the data range and EditPaste Special(in place)AddOKesc.

2. Select the column then DataText to ColumnsNextNextFinish


Gord Dibben MS Excel MVP

On Wed, 10 Jan 2007 11:22:02 -0800, Nancy
wrote:

Quick Version:
I've changed the format on a column of numbers, text and number-text
combinations to text format in order to make it match another file I'm
comparing with. The comparison is not finding ANY matches right away. I can
go back and double click in a cell (as if to edit it) and then press the
enter key and VIOLA! the match appears. It is almost as if I'm having to
finalize EACH INDIVIDUAL cell to make it work, which will not be effiecient,
as I've got 8,000 cells to click on.

QUESTION:
Is there a way to "finalize" this format change globally without having to
click on each cell?

Details:
We receive a LARGE price sheet (DOC A) from our vendor periodically. The
part number column is formatted as text. There are some alphanumeric values
in the column, but the majority are in this format: 12345.60.

I export a CSV document from my product database with the current pricing
(DOC B) and want to compare this document with the one from our vendor, among
others also in text format. When I open this CSV document, I format the part
number column to text to match the others.

The formula that I use on DOC A (Vendor sheet) looks at DOC B (our current
pricing) for the part number. If there is a match, then it dispays the
retail price in this column where my formula is. If there is not a match, it
dispays "Add To Site".

My formula is:
=IF(A376="","",IF(ISNA(MATCH(A376,Source!A:A,0)), "Add To
Site",VLOOKUP(A376,Source!A:B,2,0)))

90% of the columns show "Add to Site" even though we have 90% of the items
on our site. I can go to the sheet that I manually formatted to text, double
click in one of the cells containing the part number, press my enter key and
then the match shows up on our vendor sheet instantly.