Trim cell results

**Dial1** · 07-19-2010, 06:31 PM

I have a data download from SAP. The format the data comes into excel is with 8 spaces then the data. I would like to trim the 8 spaces to only 4. Is there a function like Trim() but that only trims a selected amount of spaces?

**teylyn** · 07-19-2010, 06:45 PM

You could do something like

=right(A1,len(A1)-4)

or

Please Login or Register  to view this content.

cheers

**martindwilson** · 07-19-2010, 06:49 PM

or
=rept(" ",4)&trim(a1)

**Marcol** · 07-19-2010, 06:56 PM

Try this

Please Login or Register  to view this content.

Or more precisely

Please Login or Register  to view this content.

N.B.
Char(32) = "Space "

Hope this helps

**martindwilson** · 07-19-2010, 06:59 PM

=SUBSTITUTE(TRIM(A1)," ",REPT(" ",4)) wont work as once trimmed a1 has no spaces
but here,s another
=REPLACE(A1,1,4,"")

**Marcol** · 07-19-2010, 07:09 PM

Hmm .... martindwilson

I disagree, seems to work for me.

The second alternative is less error prone.

[EDIT]
Might be the difference between Trim in Excel and VBa
VBa removes leading and trailing spaces
Excel Formula removes all excess spaces

**martindwilson** · 07-19-2010, 07:17 PM

wanna bet? lol

**Marcol** · 07-19-2010, 07:28 PM

Hi martin

I re-read the question and you are correct, I assumed the spaces were within the string.

My apologies......

Why should anyone want leading spaces?.....

**martindwilson** · 07-19-2010, 07:34 PM

......................................................................ah now there's a thing! oops blast those spaces lol

**Dial1** · 07-19-2010, 07:37 PM

Thanks for the responses all. I dont would rather not have the leading spaces, but don't want to rebuild the whole model that someone else have created either.