Hello all,
Once a week I am given a set of data in excel which has 6 columns which should only include dollar amounts. However, sometimes the input data I receive includes text that a user puts in such as "FLAG", "N/A", "UNKNOWN", etc. I can not control what text is written in the cells, it is just given to me.
In any event, I just need to make anything that is text in the any of these fields "0". I don't really care what is written in the cell, I just need to change it to zero. The cell could really say whatever the user puts in, so there is an endless possibility of text or phrases that could end up in one of these cells.
My problem: I need to remove any text from the fields and make them "0", while still maintaining the dollar amounts that are already in each field (which also includes cents, so I need to keep the period that separates the dollars and cents in the formatting)
Example: I want to take excel data that has the text in red in the table below
| Column 1 | Column 2 | Column 3 | Column 4 | Column 5 | Column 6 |
| 3.00 | 5.00 | 5.00 | UNKNOWN | 6.00 | 19.00 |
| 1.00 | 2.00 | 3.00 | 4.00 | 5.00 | 15.00 |
| 3.00 | FLAG | 5.00 | 3.00 | 16.00 | 27.00 |
| 12.00 | 9.00 | N/A | 12.00 | FLAG | 33.00 |
| 3.00 | 5.00 | 5.00 | 1.00 | 6.00 | 20.00 |
And replace with 0s as shown in this table.
| Column 1 | Column 2 | Column 3 | Column 4 | Column 5 | Column 6 |
| 3.00 | 5.00 | 5.00 | 0.00 | 6.00 | 19.00 |
| 1.00 | 2.00 | 3.00 | 4.00 | 5.00 | 15.00 |
| 3.00 | 0.00 | 5.00 | 3.00 | 16.00 | 27.00 |
| 12.00 | 9.00 | 0.00 | 12.00 | 0.00 | 33.00 |
| 3.00 | 5.00 | 5.00 | 1.00 | 6.00 | 20.00 |
Anyone have any suggestions?
