My bet is that they're not really trailing spaces...

Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.aspx

Then you can use this kind of formula:
=substitute(a1,char(##),"")

mathel wrote:

Hi Dave, you were right, removing the trailing space reverted the data back
to a number (like 5.88834E+18) making my workbook almost unusable. I tried
both formulas you suggested, =trim(a1), does nothing and I have no idea why,
the formula actually shows exactly as input as if it was text.

The 1st formula "=vlookup(a1&" ",sheet2!a:b,2,false)", unfortunatly does
not work either. It will not match any data, whether there is a space or not.

I'm still trying to find a work-around the problem!
--
Linda

"Dave Peterson" wrote:

If all you're missing is that additional trailing space character, maybe you
could change your =vlookup() formula:

=vlookup(a1&" ",sheet2!a:b,2,false)

I hate "fixing" the problem in the formula, though. I would never remember to
"fix" it all the other formulas.

Maybe you could insert a new column and use a worksheet function like:

=trim(a1)

to remove the trailing space.

Or in code:

Dim myRng as range
dim myCell as range
dim wks as worksheet

set wks = activesheet

with wks
set myrng = .range("b1",.cells(.rows.count,"B").end(xlup))
end with

myrng.numberformat = "@" 'text
for each mycell in myrng.cells
mycell.value = trim(mycell.value)
next mycell

===============

Doing the edit|replace (even in code) will make those 20 digit text numbers into
real numbers--and excel only keeps track of 15 significant digits. You may find
that you have 5 zeros at the end of your number.




mathel wrote:

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


--

Dave Peterson


--

Dave Peterson