How to remove blank spaces from a copied webpage table

[huh?]

Hi
Description of Problem:
Copied and pasted an internet webpage table into a worksheet. (There are going to be multiple workbooks, with multiple worksheets, each worksheet containing one table that has been pasted.) All cells have what appears to be a space before the beginning of the value, whether numeric or text. I am trying to find a better way to remove these spaces rather than individually going thru each cell and deleting these leading spaces. These spaces have to be removed from the numeric values otherwise summing them (from a column) into a cell, at the bottom, doesn't work.

Solutions Attempted:
1) Used Trim function in following manner. A1:D100 were the cells I wished to trim. In cell E1 put =TRIM(A1) in it. Then copied cell E1 and then selected cells E1:H100. Right-click and pasted. I had now trimmed all of the columns. Then I selected E1:H100 and did copy. Then selected A1:D100 and did paste special, and selected Values. Problem was the spaces weren't gone.
2) Tried Clean instead, just like above, and unfortunately, with the same lack of results.
3) I assumed at this point the blank was char 160, so I used the following function in cell E1 to get rid of the character: =SUBSTITUTE(A1,CHAR(160),"")
The message, "No Ref" was displayed in the cell.

So now I am at a loss at what to do. For those of you who would suggest a VBA solution, I wouldn't mind using VBA, but not sure what to do once a function has been placed in a standard module. i.e. If I use/put the function in a cell on the same worksheet, will it then automatically run the function and format the sheet. Any help would be greatly appreciated. Thanks

 

[huh?]

Seti,
I retried the substitute fxn and it appeared to work. This is what I did:
Used substitute function in following manner. A1:D100 were the cells I wished to remove char 160. In cell E1 put =SUBSTITUTE(A1,CHAR(160),""). Then copied cell E1 and then selected cells E1:H100. Right-clicked and pasted. I had now removed all of the char 160 values from the columns. Then I selected E1:H100 and did copy. Then selected A1:D100 and did paste special, and selected Values. The spaces were gone, but now little green triangles appeared in the upper left corner of each cell and the auto sum at the bottom of numeric columns showed 0.00 indicating that none of the the numbers were being added up. When I double clicked on a cell, ran the cursor thru the contents, and then clicked on another cell, the contents of the cell were added in the auto sum cell below and the little green triangle in the cell disappeared.
Any ideas why this is happening and how to correct it?

Nimrod,
Thank you for the macro. Once I place it in a standard module, how would I run it? By placing the function in a cell? I don't want to use a user form if I can avoid it.

Ekim,
Thank you. Very nice.

 

[Nimrod]

Huh:
Macros require no forms to run just ...
1. Select you sheet you want the macro to process
2. Run macro by Going to "Tools...Macro...Macros..." and double click on the name of the macro you want to run in the macro list.... Boomba the active sheet will be process.

 

[huh?]

Thanks Nimrod. ray:

For those of you who read this afterwards. The small sub by Nimrod which removed char 160 was a very good solution. Here is his macro with some modifications added, including autofiting the columns. For more info on the modifications, check this link:
http://www.mrexcel.com/board2/viewtopic.php?t=87636&highlight=auto+trim+sub

Sub ReplaceChr160()
Cells.Replace What:=Chr(160), Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False
With ActiveSheet.Cells
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
With .Font
.Name = "Arial"
.FontStyle = "Normal"
.Size = 10
.ColorIndex = xlAutomatic
End With
.RowHeight = ActiveSheet.StandardHeight
End With
Columns("A:IV").EntireColumn.AutoFit
End Sub

Thanks to all the rest of you for your suggestions!!!

 

[Nimrod]

Huh
Thx ,
Have you tried the other solution I posted "MakeStringsIntoNumbers" as it not only rids you of the spaces but also transforms number strings into usable numbers.

 

[huh?]

Yes Nimrod, I did, but I got weird numbers back. For example $12.97 changed to $168.22. Mind you, I only tested the manual steps you listed, not the macro... I probably did something wrong.
Anyhow the simpler solution you provided works.

 

[pedro-egoli]

G'day Huh,
Try this it’s a bit complicated but might work.
It appears that the space is treated by excel as text and you no doubt are getting the #VALUE error.


Go to 'Data', then 'text to columns' then 'Fixed width' then 'Next' and then with the cursor click immediately to the left of the data. Click next and finish and it will produce two columns. One with the two spaces and another with the data you want already to plug into your formulas etc. By selecting the entire column before doing this routine you can fix the entire column of data.

As I said it is complicated but it gets rid of the space into the second column. Of course you will need to allow for the extra column initially and then delete the original one in due course.

Good luck
Pedro

 

[huh?]

Thanks Pedro for the info.
However, I already have a working solution (macro) that I listed above.
Have a good one.