Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Need to trim leading spaces from cells

  Asked By: Ayden    Date: Sep 30    Category: MS Office    Views: 1079
  

I have a spreadsheet with alpha characters that have leading blanks.
I want to remove the blanks so that they are left justified.

I do not see how the Trim or Ltrim function can help me.

Share: 

 

5 Answers Found

 
Answer #1    Answered By: Dirck Jansen     Answered On: Sep 30


Let's assume your current sheet is called "Sheet1". Make a new sheet
called "SheetNew" and in A1 of the new sheet, type this:

=TRIM('Sheet1'!A1)

Copy this across rows and columns on the new sheet until you have
captured all the dae from Sheet1. Then select all cells  on SheetNew, and
hit CTRL+C. With all cells still selected, go to the Insert menu, choose
Paste Special and in the dilaog, select 'Values'. Click OK.

You can now delete Sheet 1 and SheetNew contains all your data, trimmed
as required.

 
Answer #2    Answered By: Calais Bernard     Answered On: Sep 30

In following your instructions I encountered the following:
- I entered the formula as shown without the quotes around Sheet1
- I copied this formula accross the area (cells/rows) where I'll paste
- When I click on Insert Menu I don't see the Paste SPecial option
- if I do a right click on the area selected that has my data I get
the Insert Menu and select Past SPecial, and Values

Here is what I get:
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?

the formula looks like this: =TRIM(ALL!A1) All = spreadsheet  name
Thank you for your assistance and time.

 
Answer #3    Answered By: Calvin Banks     Answered On: Sep 30

Sorry, I meant the edit menu, not the insert menu. It looks like you
missed out the CTRL+C step. Leaving out the quotes around the sheet name
is OK only as long as your sheet name has no spaces  in it; otherwise
they need to be there.

Just to clarify, once you have the formula throughout your new sheet,
select ALL CELLS and then CTRL+C. While they're all still selected, then
do the Paste Special.

 
Answer #4    Answered By: Ralph Murray     Answered On: Sep 30

Your guidance
enabled me to write the following procedure. I am sure this can be
done more efficiently.

Sub TrimBlanks()


' Removes Leading Spaces -
' Be Sure to rename Wrksheets in 3 places and
' notice range begins on Row 4


Dim X As Object
Dim r As Integer
Dim c As Integer

Dim MyString, TrimString

' Be Sure to RENAME Wrksheets and
Worksheets("REP").Activate

Range("A4:A65336").Select

Do While ActiveCell.Value <> ""
Set X = ActiveCell

MyString = ActiveCell
TrimString = LTrim(MyString)

r = X.Row + 0
c = X.Column + 0

' Be Sure to RENAME Wrksheets and
Worksheets("REP").Cells(r, c).Value = TrimString

'__________________________________________________________
r = X.Row + 1
c = X.Column + 0
ActiveCell.Offset(1, 0).Activate

Loop

Range("B4:B65336").Select

Do While ActiveCell.Value <> ""
Set X = ActiveCell

MyString = ActiveCell
TrimString = LTrim(MyString)

r = X.Row + 0
c = X.Column + 0

' Be Sure to RENAME Wrksheets and
Worksheets("REP").Cells(r, c).Value = TrimString

'__________________________________________________________
r = X.Row + 1
c = X.Column + 0
ActiveCell.Offset(1, 0).Activate

Loop

End Sub

 
Answer #5    Answered By: Keith Marshall     Answered On: Sep 30

Could you be missing out a step?

1. Copy the formula to all the cells  you need on your new sheet

2. Re-select the whole area and copy

3. Then go for paste special - values. I would tend to do this from A1 rather
than keeping the whole area highlighted - this way I think you should show paste
special on the edit menu.

 
Didn't find what you were looking for? Find more on Need to trim leading spaces from cells Or get search suggestion and latest updates.




Tagged: