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

#### seattletimebandit

##### Board Regular
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!

Code:
``````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``````

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
As I Understood that you want every cell to stay the way it is and just remove the "<" Sign!
Code:
``````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

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!
Code:
``````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

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.

Replies
2
Views
159
Replies
9
Views
320
Replies
5
Views
152
Replies
7
Views
326
Replies
1
Views
145

1,203,513
Messages
6,055,832
Members
444,828
Latest member
StaffordStag

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back