Consistent formatting en masse (dates, percentages, numbers glitch)

[Uncle_Chester]

I have a spreadsheet with a mix of data formatting I’m trying to make consistent. I’m trying to show percentage ranges like this: 0, 1-5, 6-25, 26-50, 51-75, 76-100. Half my spreadsheet is set up like this, but the other half was set up like this: A (0), B (1-5), C (6-25), D (26-50), E (51-75), F (76-100). To make them all consistent, I have done the following:

1 – I converted all the letters to their corresponding percentage ranges using Find/Replace (Find/Replace all the ‘A’s with ‘0;’ Find/Replace all the ‘B’s with ‘1-5,’ and so on) – FAIL: The only way that would work is if I added the % symbol to the end of each range, so I was left with half my cells saying 1-5, 6-25, etc. and half my cells showing 1-5%, 6-25%.

2 – To get rid of the % symbol, I went through and Find/Replaced ‘%’ with ‘(nothing)’ – FAIL: that changed all the ranges back to dates.

3 – I selected all and hit format cells. I tried general, number, text, all to no avail.

4 – Then I copy/pasted all the cells into a Word and Notepad document, removed the formatting and Paste Special  text only back into Excel. That just auto-converted it back to dates again.

5 – Then I tried Find/Replace with what was showing up in the cells (5-Jan). Find/Replace ‘5-Jan’ with ‘1-5.’ Excel then says it doesn’t recognize any instances.

6 – Then I looked at what was actually in the cells and tried Find/Replace that way. Find/Replace ‘1/5/2013’ with ‘1-5.’ Then Excel says it’s made all the changes but nothing changes.

I’m out of ideas

[mrice]

First format all your cells as text. Select - right click - cell format - text.

Paste the following into a new module in the VBA editor (Alt F11)

Sub Test()
For Each Cell In Selection
    If InStr(Cell.Value, "%") > 0 Then
        Cell.Value = Application.Substitute(Cell.Value, "%", "")
    End If
Next Cell
End Sub

Select the cells that you want to change and run the macro.


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007/2010.

[FDibbins]

Not sure I understand what your problem is? when I extract unique entries from all columns, I get...
0
1-5
6-25
?
26-50
51-75
76-100
DRIED UP - NO SAMPLE
too turbid to tell