Excel/Cell format issue.
My issue revolves arround changing the content of many cells at once.
I have copied an "HTML grid" into excel (2007.
The worksheet is fine and the cells have the content that they should.
Each cell contains either a single letter or a time eg:
The letter can be any one of 6 different options.. "L" "R"...
The time is in the format 08:30-17:00 (again about a dozen different possibilities.
All these values are hyperlinks and get imposrted as such.
What I would like to do is twofold.
1. Convert the hyperlinks to Text (I have tried formatting the box using the cell/format/text option but it didn't work. I am having to delete the hyperlink and retype the letter.
2. Covert the cell that contains a "time" (doesn't matter what value) to the word "IN"
The other ooption I have is to "count" the cells that contain the hyperlink values. But I need to differentiate between a cell containg "time" and one containing a "letter"
I found your question in the question pool.
If you still need an answer.
You might want to do this on a copy of your sheet.
to get rid of they hyperlinks, you can go to the vba editor and execute a single command.
1. Activate the sheet with the hyperlinks
2. Do Alt+F11 to get to the visual basic editor
3. do ctrl+G to make the immediate window visible. It should be in the lower part of the visual basic editor
4. put in this command in the immediate window
anywhere in that string and hit enter
5. Do alt+F11 to return to execl and the hyperlinks should be gone and the displayed value of the hyperlink remaining in the cells
If all your cells are of the time you show (with a hypen), then you can do this to count those cells
You can replace A:L with a specific range of cells you want to address or do entire columns such as I show. The formula should not be in the range you are counting.
You can use =countA(A:L) to get a count of all cells with an entry.
Your picture was too small to read, so I am assuming the countif and count will work for you.