trim function

**noobnxcl** · 02-11-2009, 12:23 AM

is there a trim function for numbers?

i downloaded some data and has leading spaces. trim function does not work, how can i get rid of the spaces with numbers in the cell?

thank you.

**shg** · 02-11-2009, 12:49 AM

Make sure the cells are not formatted as text. Then select the data and do Data > Text to Columns, Finish.

**noobnxcl** · 02-11-2009, 12:58 AM

does not seem to work.

**Trebor76** · 02-11-2009, 01:51 AM

The imported data contains spaces and nonprinting (see here for more info).

As such I've created the following formula which will hopefully do the job - if there is an error (there is no number for instance) a zero is returned:

=IF(ISERROR(TRIM(SUBSTITUTE(A1,CHAR(160),CHAR(32)))),0,TRIM(SUBSTITUTE(A1,CHAR(160),CHAR(32))))

HTH

Robert

**noobnxcl** · 02-11-2009, 02:12 AM

thank you for your help. however, does not seem to work. i have used the formula and tried doing calculations and all i get is #value.

**DonkeyOte** · 02-11-2009, 04:21 AM

The below should work:

Please Login or Register  to view this content.

As shg points out however coercing the data is the best solution...

You can use Edit -> Replace, 2 methods:

1 - in a blank cell enter =CHAR(160)
copy that cell, highlight your data, Edit -> Replace:
Find What: paste your copied cell (will appear as blank but don't worry)
Replace With: leave empty
Click Replace All

2 - if you have a numeric pad available
Highlight your data, Edit -> Replace:
Find What: Hold ALT + type in 0160
Replace With: leave empty
Click Replace All

The only non-numerics that would persist from the above process would be the stand alone hyphens... you should not run a replace of the hyphen char (45) if you have negative values in your range as the negatives would thus become positive. However, leaving the stand alone hyphens as text values should not impact on your calcs... ie negatives will have been coerced to number so all numbers can be summed etc...