Hi Mynda, its me again....sad face......but smilie face that i have you. Hope you are keeping safe in the pandemic.
I haven't encountered this one in a while....its perplexing me....please help.
I use VLOOKUP a lot but sometimes I am stumped because of errors like these.
Thanks much.
Hi Grace,
Did you intend to attach a file? Be sure to click the Start Upload button after selecting your file.
Mynda
ooops....thought i did.
Hi Grace,
The problem is the numbers in sheet2 (3) are text, and the numbers in Sheet2 (2) are numbers. You can tell the numbers in sheet 2 (3) are text because when you select cells B12:B13 the status bar (bottom right) only shows a count, whereas if they were numbers, like in Sheet2 (2) they also show a sum.
You either need to convert the Sheet2 (2) values to text or the Sheet2 (3) to numbers. You can use Text to Columns (data tab) to fix them if it's a one off task, or Power Query.
Hope that points you in the right direction.
Mynda
There are additional Unicode characters in your 'numbers'. Sometimes there is a char 8237 at the start and there appears to always be a char 8236 at the end. CLEAN doesn't understand those so it fails to remove them. You could add a function like this to a new module in your workbook:
Function CleanNonNumeric(ByVal dataIn As String)
' removes anything that is not a number, period or - sign from text
Dim n As Long
For n = 1 To Len(dataIn)
Select Case AscW(Mid$(dataIn, n, 1))
Case 45, 46, 48 To 57
CleanNonNumeric = CleanNonNumeric & Mid$(dataIn, n, 1)
Case Else
' ignore
End Select
Next n
End Function
and then the formula in B12 becomes:
=CleanNonNumeric(A12)+0
The +0 is to convert the result to an actual number rather than text.
Thank you very much Mynda and Velouria.......now works.
