Convert cell value that has a number and a character to be able to use the number in calcs/graphs

[seattletimebandit]

Good day all.

I'm working with a bunch of laboratory results that has data qualifiers for results that are not detected and flagged with a "U" data qualifier for "undetected". However, my range of cells has a mix of numbers (detections) and some are numbers with the not detected "U" value. An example of the cells with the number/text value are: "1000 U" or "0.100 U". I want to be able to ignore the "U" to the right of the number then use that number in a graph or calculation. Is it possible to select a range and run some code to do this, even if some cells have only the detected number value?

My other option is to split all the columns using the Data -> Data Tools -> Text to Columns function in the Ribbon which would put the "U" (or blank space if no "U" exists) into the adjacent column, then convert the numbers that are free of the "U" for data calcs, yet maintaining the "U" value for the adjacent cell. BUT, I can only do that one column at a time, and there has to be a blank column to the right, otherwise I would overwrite the data in that adjacent column. Again, I have a macro that inserts a new column every other column, but that's just another step I'm trying to avoid.

Thanks!
stb

 

[alansidman]

Option Explicit


Sub delU()
'Removes any U's from Data in column A
    Dim i As Long, lr As Long
    'Assumes your data is in column A
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To lr    'Assumes data starts in first row
        If InStr(Range("A" & i), "U") > 0 Then
            Range("A" & i) = Left(Range("A" & i), Application.WorksheetFunction.Find(" ", Range("A" & i)))
        End If
    Next i
End Sub

 

[seattletimebandit]

Thanks alansidman,

I can find a lot of uses for your code snippet. However, the one thing I was wondering if this can be done, is there a way to split the column so that the number and "U" are in separate columns? Noting that some cells will only have a number and no "U".

So with several columns of data, the code would first insert a new column to the right of the col with data, then if there is a number and a "U" in the first column move the "U" to the adjacent cell in the new column. The number of columns and rows is variable but this routine would be repeated for all columns.

Currently I insert a column to the right of each column of data (i have a little macro that makes this faster), then I can do the Text to Column from the Ribbon. One column at a time. For small tables not a problem, but for 30+ columns with 600+ rows, this gets onerous.

Or I could use a formula to return the "U" by doing this: =RIGHT(A2,1), pasting this formula adjacent to each cell with a "U". But that is tedious and time consuming and the odds of mistakes is high. So I would need to have code look at the cell: If <> "U" then "" return nothing to adjacent cell , else if = "U" then extract and return "U" into adjacent cell.

stb

<colgroup><col width="64"></colgroup><tbody>
</tbody>

 

[alansidman]

Sub delU()
'Removes any U's from Data in column A
    Dim i As Long, lr As Long
    'Assumes your data is in column A
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Range("B1").EntireColumn.Insert
    For i = 1 To lr    'Assumes data starts in first row
        If InStr(Range("A" & i), "U") > 0 Then
            Range("B" & i) = "U"
            Range("A" & i) = Left(Range("A" & i), Application.WorksheetFunction.Find(" ", Range("A" & i)))
        End If
    Next i
End Sub

 

[Peter_SSs]

Another option might be to use additional columns to the right of the whole table with formulas like below. For example, if column K might contain "U" values ..
=SUBSTITUTE(K2,"U","")+0
Copy that formula down the length of the table and use that (numerical) column for your graphs. Repeat for other columns that might contain "U"