Removing Spaces (?) from a cell

[AK262007]

Hi all

Unfortunately the problem is not as simple as the title may sound

Basically I have attached a example of what I mean

I need to do a lookup on these cells but for some reason they all appear to have some kind of space characters in each cell which you can see if you double click on them. I have tried trims, text to columns, find replaces and none of these seem to work

I have a huge list of these numbers / codes and they all have the weird space kind of characters at the end

Can anyone give me a solution on how to remove them so my lookup will work ?

Thanks very much !!

Al

EXAMPLE.xls

[NBVC]

try selecting the range of items and go to Data|Text to Columns.

Select Delimited and click Next

Check Space and click Finish.

[zbor]

does =LEFT(A2;FIND(" ";a2;1)) helps?

[oldchippy]

Hi,

It seems you have 3 extra characters, you could try

=LEFT(A2,LEN(A2)-3)

[AK262007]

Thanks for the reply

Unfortunately it seems that any number containing "E" (Example updated) then go into a strange format

Any other idea's or any way of stopping this ?

Thanks again

Al

EXAMPLE.xls

[zbor]

You must change format because A cells are formated as text....

Also, I make mistake in my formula, you must take -1:

=LEFT(A2;FIND(" ";A2;1)-1)

[NBVC]

Thanks for the reply

Unfortunately it seems that any number containing "E" (Example updated) then go into a strange format

Any other idea's or any way of stopping this ?

Thanks again

Al

Attachment 38964

Repeat the same as I said before... but after selecting Space in the 2nd frame click Next and select Text from the top left column data format area and then click Finish...

[DonkeyOte]

EDITED...

you chould think about using SUBSTITUTE, ie:

SUBSTITUTE(A2,CHAR(160),"")

It's not clear from your post whether you're using these values as criteria in your VLOOKUP or they are the left most column of the range... so it's hard to provide further insight.

EDIT: Nice tip NBVC.

[zbor]

Formula works... It will remove all spaces at the end...

It won't work if space is between words.. (then it will show only first word)

[DonkeyOte]

Yep, my bad, I edited my post (belatedly) ... NBVC's approach to alter underlying values in first instance is the most robust I think.

[oldchippy]

you chould think about using SUBSTITUTE

You edited this?

[AK262007]

Thanks to you all for the help on this one - all of them worked in the end and I have decided to use the solution from Zbor as this seems to be a nice tidy solution

Thanks again !!!

[zbor]

\o/ Did I earned one reputation point now

(P.S. Don't forget -1 )