Log In
Register
Home
May 23, 2020
Offline
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.
See Answer
July 16, 2010
Offline
Hi Grace,
Did you intend to attach a file? Be sure to click the Start Upload button after selecting your file.
Mynda
May 23, 2020
Offline
ooops....thought i did.
July 16, 2010
Offline
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
Answers Post
Trusted Members
Moderators
November 1, 2018
Offline
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.
1 Guest(s)
