VBA - Delete space (cell start)

VBA

VBA

New Member
Joined
Jan 19, 2009
Messages
39
Heya,

I often have to work in Excel-spreadsheets which provide values to me in a rather unpractical way. For some reason, every cell entry starts with a space, followed by the value. It makes it impossible to start calculating with the values right away. :rolleyes:

I tried
Code: 
replace: " " with: ""
But that way all the spaces in the sheet are removed, not just the spaces at the start of each cell. I would like to avoid that, as it makes the descriptions a bit difficult to read...

So, any ideas on how to write a little macro (or a tric in Excel itself) which I can use to quickly get rid of those spaces and start calculating? :biggrin:

Thanks in advance.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
=TRIM(A1)

Mind you, if these are supposed to be numbers then you'd better convert them to values as well:

=TRIM(A1)+0
 
Upvote 0
Thanks :biggrin:

And some are indeed numbers in the sense of values, others are data, others are words... it's a bit of a mess but I try to clean it up ;) Thanks for giving me this tool to do it a bit faster.
 
Upvote 0
Then make it a bit more flexible:

=IF(ISERROR(TRIM(A1)+0),TRIM(A1),TRIM(A1)+0)

That will avoid errors for the cells which actually contain text.
 
Upvote 0
Perhaps

Code: 
Sub NoBL()
Dim c As Range
For Each c In ActiveSheet.UsedRange
    If Left(c.Value, 1) = " " Then c.Value = Right(c.Value, Len(c.Value) - 1)
    c.Value = c.Value
Next c
End Sub
 
Upvote 0
smiley_clap.gif
Great :biggrin:
Thank you both
 
Upvote 0
:(

Ok, I thought I could actually do something :oops: Big mistake :eek:

Point is, macro "NoBL" doesn't do a thing for my sheet.

Trying to make a macro with "TRIM(range)" incorporated in it was my thought, but I fail... I figured I could just copy the whole worksheet (Sheet1) but then without the spaces (Sheet2)
I can just do this, but how do I write a macro for it such that I only have to run it, instead of typing the formula into Sheet2 and expand it to all columns and rows? I tried to record it but it says it is "impossible to record"
Code: 
For i=1 to 1000 'rows
     For j=1 to 100  'columns
           Range("ji") ="IF(ISERROR(TRIM(ji)+0),TRIM(ji),TRIM(ji)+0)"
     next j
next i
I just don't see how I walk through the columns and rows... I know i and j isn't the way... o_O
 
Upvote 0
If NoBl does nothing then maybe they aren't spaces. What does this return:

=CODE(LEFT(A1,1))

(substitute A1 with the address of a cell with a leading 'space').
 
Upvote 0
I figured it out :) Or well - I figured out what it is :p

The code I should use isn't TRIM, but "TRIM(SUBSTITUTE(A1),CHAR(160),CHAR(32)))". As you say, it might not be a space - it IS a space, but not the one that is recognized by the TRIM-function.

As Excel-help says, using substitute means:
"Replaces each nonbreaking space character (Unicode value of 160) with a space character (ASCII value of 32) by using the SUBSTITUTE function, and then removes the leading and multiple embedded spaces from the string"

I still don't get how to put this into a macro with going through columns and rows though - but maybe that is a more general question:
"How do I make a loop through both columns and rows" ;) I'm full of questions :p

And VoG, not sure where to fill in that code... But in a new spreadsheet, refering to the old, I get "160" for every cell ...
 
Last edited:
Upvote 0
Try this:

Code: 
Sub NoBL()
Dim c As Range
For Each c In ActiveSheet.UsedRange
    If Left(c.Value, 1) = Chr(160) Then c.Value = Right(c.Value, Len(c.Value) - 1)
    c.Value = c.Value
Next c
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Completely cleaning account numbers and storing with a specific format
Replies
2
Views
612
diversification
D
Y
  • Question
How to delete contents of all cells with a formula that results in an empty space
Replies
7
Views
564
yslguru
Y
A
  • Question
Show Data Validation Lists in Next Row of Table
Replies
0
Views
438
AndyPandy27
A
M
  • Question
Workbook Event Macros Triggering in Other Workbooks
Replies
7
Views
850
Anthony P Rose
A
D
  • Solved
VBA find next blank, display value from another cell in the same row, input value in blank
Replies
4
Views
432
Legacy 143009
L

Forum statistics

Threads
1,215,050
Messages
6,122,868
Members
449,097
Latest member
dbomb1414

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top