Is there any way to have the formatting of a cell translated to a number?

[tb813]

I have a big database of stuff (thousands of rows) where I've been using the background color of cells as a way to sort of check off the status of an item. I just realized yesterday that the color approach leaves me no recourse if I want to sort by items that have a different status. At this point, I'm mostly just resigned that I'll have to make up for my mistake the hard way (by manually entering a number for each item, one-by-one), but I just wanted to check in here and make sure there's not some easier way to do it before I put all that time in.

[Sam Capricci]

not sure I'm totally following but if you have columns of numbers that are formatted as text and you want to convert them quickly here is the fastest trick I know of...
highlight all the areas that have them (even if some are already numbers) then go to the data tab at the top, click on text to columns then when the box opens, if delimited is clicked leave it go, if not click it then just hit finish and they'll all convert to numbers.

[FDibbins]

Not sure if this will help, but you can filter by color (as long as teh color was applied manually). You could then maybe add a number on a helper column to that filter (you can copy/paste in a filter, and the hidden rows wont be pasted to), select the next color, add a 2nd number, rinse amd repeat.

This will give you a helper column with a bunch of numbers in your helper that you can then use for all sorts of things - sort/sum/count/avg etc

Give this a try and if you have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[tb813]

not sure I'm totally following but if you have columns of numbers that are formatted as text and you want to convert them quickly

I didn't mean that kind of formatting, I meant like some cells have a blue background, some have a gray background, etc.

[tb813]

Not sure if this will help, but you can filter by color (as long as teh color was applied manually). You could then maybe add a number on a helper column to that filter (you can copy/paste in a filter, and the hidden rows wont be pasted to), select the next color, add a 2nd number, rinse amd repeat.

This sounds like it would be a solution, but I'm not clear on how to do that. When I select Data > Filter, it doesn't seem to have any options that can do what you describe.

[FDibbins]

do you know how to set/apply filters?
(sorry if that is a dumb question lol)

And are you really using 2003?

[tb813]

No and yes, unfortunately. I'm guessing something like this would probably be easier to do in a newer version.

[FDibbins]

hmm I dont recall what the filter options in 2003 are now. In 2016, there is a "filter by color" option - it may not have been included in 2003.

Is there any logic to how the colors were applied in the 1st place?
Can you upload a small (clean) sample file, showing what you have and what you want?

[tb813]

In 2016, there is a "filter by color" option - it may not have been included in 2003.

According to this page on Stack Overflow, there isn't any way to do it natively, but it can be done with an add-on called ASAP Utilities. You know anything about that? It looks like the home version is free, but I don't want to install it if it will cause other problems.

Is there any logic to how the colors were applied in the 1st place?

It's basically just like checking off items on a to-do list, but a really freaking long list.

Can you upload a small (clean) sample file, showing what you have and what you want?

I can still do that if you really want me to, but I can tell you with confidence that there's nothing in the actual values that will make it sortable along the same lines as the highlighting.

[FDibbins]

I have used ASAP before, it gives LOTS of options (perhaps too many lol)
I will take your word that the color is (effectively) random

[tb813]

I have used ASAP before, it gives LOTS of options (perhaps too many lol)

Is it the kind of thing that can be installed for just this one task, and then easy removed without a trace after I'm done (if I didn't want all those options to still be around)?

[FDibbins]

Yup, it should be

[tb813]

Cool, I guess I'll check that out then.

[FDibbins]

Thanks for the feedback