Trailing Space / Format Question

[Guest]

Hey gang

I have a spreadsheet in Excel 2003 that has values imported from a Crystal
report. All the numbers have a trailing space, so I can not format them to
show dollar signs, decimals, comma seperators, etc...

I have tried to use the TRIM function, then copy/paste>Values only, but the
values only paste gives me back the trailing space.

Any ideas?

 

[Guest]

Try this formula in a help column


=--TRIM(SUBSTITUTE(A1,CHAR(160)," "))


copy and paste special as values and then use number formatting




Regards,

Peo Sjoblom

 

[Guest]

Well, this works for the numbers. But now the cells that contain text show
up as "#Value!"

Any slick way around that? If, then statement perhaps?

Thanks Peo

~hagen

 

[Bob Phillips]

=IF(ISERROR(--TRIM(SUBSTITUTE(A1,CHAR(160),"
"))),A1,--TRIM(SUBSTITUTE(A1,CHAR(160)," ")))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

That did it.

You guys are amazing. Thank you.

=IF(ISERROR(--TRIM(SUBSTITUTE(A1,CHAR(160),"
"))),A1,--TRIM(SUBSTITUTE(A1,CHAR(160)," ")))

--

HTH

RP
(remove nothere from the email address if mailing direct)