Trailing Space / Format Question
 

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/pasteValues only, but the
values only paste gives me back the trailing space.

Any ideas?

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



"hagen" wrote:

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/pasteValues only, but the
values only paste gives me back the trailing space.

Any ideas?

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

"Peo Sjoblom" wrote:

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



"hagen" wrote:

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/pasteValues only, but the
values only paste gives me back the trailing space.

Any ideas?

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

--

HTH

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


"hagen" wrote in message
...
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

"Peo Sjoblom" wrote:

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



"hagen" wrote:

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/pasteValues only,
but the
values only paste gives me back the trailing space.

Any ideas?

That did it.

You guys are amazing. Thank you.

"Bob Phillips" wrote:

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

--

HTH

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


"hagen" wrote in message
...
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

"Peo Sjoblom" wrote:

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



"hagen" wrote:

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/pasteValues only,
but the
values only paste gives me back the trailing space.

Any ideas?