Finding the value of a cell above a cell that meets a condition in a column

[rufuxinix]

In an Excel column I need to find the value of a cell that is immediately above.
Example: If I have cell values that end in 5, I want to extract the value of the cell above them.

 

[jolivanes]

Leading spaces? Is the result of

=Len(A2)

what it should be (the total of the visible characters)?

 

[jolivanes]

Post #11 should read "trailing spaces?"

 

[rufuxinix]

I get no result at all. I think I might be doing something wrong.

Thank you for this. However, it doesn't seem to.work. What am I doing wrong?

15	=IF(RIGHT(RC[-1], 1) = "5", R[-1]C[-1], "Nothing")
6
15
7
14
15
5
6
8
3
1
5

 

[rufuxinix]

I get no result at all. I think I might be doing something wrong.

Thanks for the input. I tried, as above, no result, system message displays.
Column is Number format, not sure what else to check.

 

[rufuxinix]

View attachment 119909

Thanks for the input. I tried, as above, no result, system message displays.
Column is Number format, not sure what else to check.

View attachment 119912

Also, enabled R1C1 Reference Style, under Options/Formulas

 

[jolivanes]

I assume that your formulae go into Column B. The cells should not be formatted as text wherever the formulae go.
Format Column B as "General" and type in the formula in B2.

 

[rufuxinix]

I assume that your formulae go into Column B. The cells should not be formatted as text wherever the formulae go.
Format Column B as "General" and type in the formula in B2.

I did that, no difference.

 

[jolivanes]

How are the values in Column A entered?

 

[rufuxinix]

How are the values in Column A entered?

Copied and pasted

 

[jolivanes]

Did you try Post #11

Physically count the visible characters and somewhere where you are sure you can enter a formula the "Len" formula.



If you want to, you can check with a macro also.

Sub Count_Characters()
Dim i As Long
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        MsgBox "Cell " & Cells(i, 1).Address(0, 0) & " has " & Len(Cells(i, 1)) & " characters!" & vbLf & _
        Asc(Right(Cells(i, 1).Value, 1)) & "  (This should be 53 if the last characters is a 5!)"
    Next i
End Sub

It looks like your cells are not formatted the way they should be. The formula shows as text.