Need help with formatting numbers in cells that have text using VBA

[seattletimebandit]

Hello all, first time here.

I have a table of data in which a lot of data appears as "<0.500 U".

I have to strip out the "<" sign, but keep the "U". I have a macro that does this (see below), but the format of the number changes to "0.50 U". I need to be able to retain the trailing zero as "0.500 U". Plus the sig figs can be different in other cells, but I need to keep the formatting for them as well:

Example of formats I need to keep:

0.500 U (always has a "U")
0.50 U (always has a "U")
0.2 U (always has a "U")
2.13 (usually has no "U", but could from time to time)
10.1 (usually has no "U", but could from time to time)
1,125 (never has a "U")

The following macro is used to find the leading "<" signs and insert a trailing "U" if not in the cell, then strip out all the "<'s" from the entire table. Numbers that need a thousands separator do not have decimals (and also don't have a "<" or "U") and some have decimals of one, two, or three significant digits. But when I run the macro, the trailing zero disappears from the "0.500 U" to "0.50 U" after running the macro below.

So I need to be able to ignore the text ("U") and format the numbers based on my criteria above. I did get the thousands separator bit to work
but it formats all numbers to not have decimals, 'cause that's what I told it to do. I just can't figure out how to format a cell with text.

Thanks for the help!

Sub ChangeQualifiers()


' created 04/10/13


Dim c As Range
    
Application.ScreenUpdating = False
     
    For Each c In Selection
        If Left(c.Value, 1) = "U" Then                  ' find only existing "U" qualifiers
            c.Value = Right(c.Value, Len(c.Value) - 1)  ' if "U's" are found then delete all "<"
            c.Value = Left(c.Value, Len(c.Value) - 1) & " " & (c.Value) ' then add a space between the number and the "U"
        
        ElseIf Left(c.Value, 1) = "<" Then                    ' now look for remaining "<'s", this is in case the data has a "<" but no "U"
            c.Value = Left(c.Value, Len(c.Value) - 1) & " U"  ' if found then add a "U" with a leading space
            c.Value = Right(c.Value, Len(c.Value) - 1)        ' then delete all remaining "<'s"


        ElseIf Left(c.Value, 1) <> "U" And Left(c.Value, 4) > "999" Then  'fix this, not working, formats numbers with decimals as whole numbers
            c.NumberFormat = "#,##0 "


        End If
        
' now format to line up right justified
        With c
            .HorizontalAlignment = xlRight
            .VerticalAlignment = xlCenter
        End With
' indent
        c.InsertIndent 1
        
' finally, bold all data that does not have a qualifer
      If Right(c.Value, 1) = "U" Then
              c.Font.Bold = False
        Else: c.Font.Bold = True
      End If
    Next c
End Sub

 

[ZAX]

As I Understood that you want every cell to stay the way it is and just remove the "<" Sign!

Sub RemoveLessSign()
Dim c As Range, Cell As Range
Set c = Range("a1:az" & Cells.End(xlDown).Row)
For Each Cell In c
    Cell.Value = Replace(Cell.Value, "<", "")
Next
End Sub

ZAX

 

[seattletimebandit]

Thanks for responding ZAX,

But my macro already deletes the "<" sign. what I'm trying to do is format the numbers that still have the "U" AFTER the "<" sign is removed. As far as I can tell, with the "U" still in the cell, the data is treated as a string, or a general formatted number. I need to format numbers differently, depending on significant digits and also retaining trailing zeros.

But thanks for the code snippet, I can use that in other applications.

As I Understood that you want every cell to stay the way it is and just remove the "<" Sign!

Sub RemoveLessSign()
Dim c As Range, Cell As Range
Set c = Range("a1:az" & Cells.End(xlDown).Row)
For Each Cell In c
    Cell.Value = Replace(Cell.Value, "<", "")
Next
End Sub

ZAX

 

[seattletimebandit]

I answered my own question as to why the trailing zero's were deleting.

It was in my ElseIf Statement:

c.Value = Left(c.Value, Len(c.Value) - 1) & " U"

I took out the "- 1" (which was deleting the right-most digit):

c.Value = Left(c.Value, Len(c.Value)) & " U"

However, I still do have the issue of needing to change the number format for all numbers that don't have a "U" in the cell. I'm working on it, and will post my solution if I figure it out. Any nudging in the right direction will be most appreciated!

To clarify: I have a range with number formats (0.500, 0.50, 0.5, 5.0, 5, 50.0, 50, 500), BUT when I get to a number that needs (but doesn't have) a thousands separator (5000), I want to add the comma (5,000) without changing the format of all the other formats (I need to keep all the decimals and zeros). If I use something like

c.NumberFormat = "#,##0"

across the range, I will get the thousands separator where appropriate, but I lose the decimal point and sig figs for those numbers that are 999 or less.