excel

[ahazaras]

I am trying to clean an excel field. I get the field from and html screen. The field is volume. It is coming across to excel with two spaces on the end. I have tried the function, find and replace but that doesn't work. I can't change the field to a number using format cell. Can anyone help. Field looks like this "5000 ". Without the quotes.

I need this field to be a number before I take it into access.

Thanks

 

[PJFry]

Hmm... now that I think about it, you really should use
=VALUE(LEFT(A1,LEN(A1)-2)) This way it converts straight to a number.

PJ

 

[Remou]

Hi
I am sure PJFry has the best method, but have you tried find and replace using Alt + 255 (hold down Alt and type 255)? This is an askii character that looks like a space and is often what comes across from the net.

 

[KenWright]

I've yet to come across a bit of data that Dave's Trimall macro can't fix in a nano second.

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[01Patrik]

Since both Trim, Left and most other functions returns a string value. Could it be so easy that you can take whichever of those solutions, and wrap a =VALUE(...) around it...?

// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[Dsuperc]

Download this utility, its free

http://www.asap-utilities.com/

There is a function, Text, remove all leading and trailing spaces, also, remove ending carrage return, and much more

 

[gizmo1973]

Ken,
Me too

Regards, Phil.

M.U.F.C. Show your true support here:

http://shareholdersunited.org/join.php?ref=125059000

"Shares not Shirts