In cell A1 there is a text string.
Formulas and macros work if I type the text into Cell A1. But if I copy the very same text from a PDF file or Notepad and paste into Cell A1, Formulas and macros won't work.
Before pasting the text string into Cell A1, I first copy the text from a PDF file and then paste it into Notepad. I then copy the text string from Notepad and paste it into Cell A1.
If I type the text, Formulas and macros works. If I copy from Notepad (or even the pdf file) and paste it, I get #VALUE or FALSE.
Here is one formula that I use:
=ISNUMBER(SEARCH("customers' inventories in January is",A1))
which returns FALSE if I paste the text string, but returns TRUE if I type it.
and here is one macro: (which returns #VALUE if I paste the text string, but works if I type the text string into cell A1)
Function REPLACETEXTS(strInput As String, rngFind As Range, rngReplace As Range) As String
Dim strTemp As String
Dim strFind As String
Dim strReplace As String
Dim cellFind As Range
Dim lngColFind As Long
Dim lngRowFind As Long
Dim lngRowReplace As Long
Dim lngColReplace As Long
lngColFind = rngFind.Columns.Count
lngRowFind = rngFind.Rows.Count
lngColReplace = rngFind.Columns.Count
lngRowReplace = rngFind.Rows.Count
strTemp = strInput
If Not ((lngColFind = lngColReplace) And (lngRowFind = lngRowReplace)) Then
REPLACETEXTS = CVErr(xlErrNA)
Exit Function
End If
For Each cellFind In rngFind
strFind = cellFind.Value
strReplace = rngReplace(cellFind.Row - rngFind.Row + 1, cellFind.Column - rngFind.Column + 1).Value
strTemp = Replace(strTemp, strFind, strReplace)
Next cellFind
REPLACETEXTS = strTempEnd Function
Again thank you very much for your time! Actually I found the solution to my problem! There wasn't any problem with the macro or the formulas... the problem was that there was a mismatch between the text string I pasted and the text I typed in my formulas and macros. for example when I pasted the following text string "... customers’ inventories ..." it didn't match what I had either in my dataset, formulas when I typed "... customers' inventories ..." . Do you see the difference ? The difference lies between ' CHAR(39) and ’ CHAR(146). Because when I type on the keyboard I actually only use ' or CHAR(39)... I didn't visually see the difference between the two symbols... Anyway, the problem is solved now and I can move on! :D Thanks again for your time.