Trim a number

[shadow12345]

Morning all,

I am using a formula
=MATCH(TRIM('Source data'!U769),'Deletions'!D:D,0)

Works fine for everything except one reference. Most of the cell in Col U contain alphanumeric value.

One cell contains "61169 " (no qoutes but lots of spaces)
if I trim the cell I can't match the result to my other sheet, but if I manually remove them I can match it.

Is it because im trimming a number? Or any other ideas?

 

[Richard Schollar]

It's because the value in the D column of the other sheet is held as a number, not as a string (of digits). Your TRIM() function returns a string of digits (ie text) and this therefore doesn't match to the numeric value in col D of Deletions

 

[shadow12345]

ok so add a value() to the trim then.... will that be ok for non numerical values?

 

[shadow12345]

nope.... hmmm

 

[Richard Schollar]

If you don't want to adjust the source data (ie Deletions) then you could run a test before you do a lookup:

=IF(ISNUMBER(TRIM(A1)+0),MATCH(TRIM(A1)+0,D:D,0),MATCH(TRIM(A1),D:D,0))

 

[shadow12345]

And the winner is.... Richard with ISNUMBER.

I should of thought of that, thanks very much.

 

[Peter_SSs]

Does this also work?

=MATCH(IF(ISNUMBER(A1+0),A1+0,TRIM(A1)),D:D,0)

 

[shadow12345]

yeap... i like that way, makes it a bit shorter.

 

[coolkidz33]

I am not sure if I was supposed to start a new thread for this or but I am having a similar problem. I am using Excel 2007 and imported a bunch of info from a pdf file. I got it mostly correct but some of the information has a space at the end and I cannot get rid of (except manually). I have tried to trim it, do text to columns, and paste special multiply by 1. Nothing works. It is obviously not a number but I cannot convert it to one unless I hit F2 and backspace the space out. So here is what I have starting in cell A2

$15,555 (space at the end)
$783 (space at the end)
$8,933 (no space at the end)
$983 (space at the end)

And so on. So if I add those up I only get 8,933 as that is the only number. I have never been able to get trim() to work. It always leaves the space there. Any ideas?

 

[Richard Schollar]

That 'space' is probably a non-breaking space which is ascii character 160 (rather than character 32 for a normal space). Rather than TRIM, try using SUBSTITUTE to remove:

=SUBSTITUTE(A1,CHAR(160),"")+0

to turn the value in A1 into a number.