Hi Linda,

I know this 'dam...' thing of unstructured data .

Are there 1 or more spaces after the 'value'?

If you don't mind to send me the file, I'll look further into.



Believing that I can handle this in the first reply.

Wkr,

JP


"mathel" wrote in message
...
Sorry, I thought the 'Find & Replace' resolved the problem, it did remove
the
space, but the data in the rows (which are 20 digit numbers) reverted to
something like 5.8834E+18 (on the Edit line, the last 4 digits are all
zeros,
so unfortunately, the problem is not yet resolved.

--
Linda


"mathel" wrote:

Yep, done this, and it resolved the problem. I guess we will have to do
this
on a daily basis to ensure none of the users pasted data with a space
included at the end.

Thanks again for all your help. This website has to be one of the best
sources of information and help the I have used to date!
--
Linda


"JP Ronse" wrote:

Hi Linda,

Is Edit/Replace find:<space replace:<nothing not the easiest way to
do?

Wkr,

JP

"mathel" wrote in message
...
I found the problem! The data we are adding to worksheet 'Exposed' on
a
daily basis is a 20 digit number, so the column in the worksheet is
formatted
to 'Text'. The data is being copied from a Word document, and we use
'Paste-Special-Text' into the spreadsheet. What I found was that if
I
double
click the number in the Word doc, there is a space at the end of it
On
all
numbers where there is a space at the end, VLOOKUP will not do a
match.

That being said, I did find a piece of code that found the number
whether
it
had a space at the end of it or not. The problem is, this code is to
replace
the text in the found row. I do not know anything about VBA, so
don't
know
how to modify it so that it would, lets say, change the cell interior
to
Yellow. The code is as follows:

Sub Replace_TExt()


For i = 1 To
ActiveSheet.Range("B:B").Cells.SpecialCells(xlCell TypeLastCell).Row

If Trim(ActiveSheet.Range("B" & i)) < "" Then

ActiveSheet.Range("A:A").Replace What:=ActiveSheet.Range("B" & i),
Replacement:=ActiveSheet.Range("B" & i).Value, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:= _
False, ReplaceFormat:=False

End If

Next i
End Sub

Is it possible to have this code 'modified' to change the cell vs
replace
text?

THANK YOU so much for all you help/comments in finding what the
problem
was
with my ws.
--
Linda


"Dave Peterson" wrote:

I don't see how using VBA would help find an exact match if you
can't do
it
using formulas.

I'd look for differences in the data.

Debra Dalgleish has lots of notes on troubleshooting =vlookup():
http://contextures.com/xlFunctions02.html#Trouble

If that doesn't help, what are you matching on--simple text, whole
numbers,
times, dates, fractions, long strings????

mathel wrote:

I've checked thru the Discussion group and found this subject has
been
asked
several times, but can't find something I can use.

I am using Excel 2003 and have a wb with 2 sheets - 'Exposed' and
'Transactions'. I need to compare Column A in ws 'Transactions'
to
Column a
in ws 'Exposed'. If there is a match, have the data show on the
same
row in
Column b on ws 'Exposed'. The number of rows on both ws can vary
anywhere
from 500 rows to as many as 5000. I know the simple answer is to
use
VLOOKUP, unfortunately, there are multiple users (and PCs) using
this
wb and
for whatever reason the formula does NOT always work and the data
is
being
missed so I am looking for VBA.

Even if the data in Column A could not be put into Column B, I
would be
happy if the cell was highlighted.

Thanks
--
Linda

--

Dave Peterson