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

 

[BJungheim]

Try pasting a sample of the text to a blank sheet in Cell B7, then put this code in E7 and copy accross one column for each character in the string.

=MID($B7,COLUMN()-COLUMN($D7),1)
This will string the text out one character per cell.

Below that put:
=Code(E7)
and copy accross. Look beneith the cell containing the blank and it will give you the CHAR() number of the blank.

This won't clean them but it will help you identify the offending spaces.

 

[huh?]

Thank you. Below is the result of checking char code. As you can see, the space that I am trying to remove is char 160. They're not lining up properly, but 160 is for the blank space at the beginning.
  $ 3 6 . 6 9
160 36 51 54 46 54 57

So why did the code I used initially in step 3 to remove char 160 not work? What is the most efficient way to clean up the data when dealing with multiple sheets? Appreciate the help.

 

[Nimrod]

Hello HuH
TO GET RID OF SPACES ...

To get rid of spaces just use the Replace tool found on the Edit Menu.

in the Find Text box hit the space bar once to tell it your looking for spaces... in the replace text box put nothing to tell it to replace spaces with nothing ... boomba your done

 

[huh?]

umm thanks Nimrod, but the space is actually char 160, so replacing spaces doesn't work. Any other suggestions?

 

[Seti]

See the attached. The substitute worked for me just fine.

Book7
	B	C	D	E	F	G	H
3	160	36	51	54	46	54	57
4		$	3	6	.	6	9
5	$36.69	7
6	$36.69	7
7	$36.69	6
Sheet1

 

[Nimrod]

To change number strings to actual numbers do the following .
1. put the number 1 in an empty cell
2. Select the cell with number 1 in it and select copy
3. Bring up Goto window by pressing F5 key
4. in the GoTo window click “Special..”
5. In “Special Window” select the “Constants” Checkbox and click OK
6. Now that all cells with constants in them have been highlighted …
7. Right click on one of the selectes cells and Select “Paste Special”
8. in “Paste Special “ select multiply and then OK…
9. Boomba done .

 

[Nimrod]

This Macro will go thru active sheet and replace all Chr (160) values with nothing

Sub ReplaceChr160()
Cells.Replace What:=Chr(160), Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False
End Sub

 

[Nimrod]

The following Macro ...
1) Removes all chr 160 from strings on activeSheet
2) Uses PasteSpecial Multiply to change number strings into actual numbers

Public Sub MakeStringsIntoNumbers()
' get rid of spaces eg Chr 160
Cells.Replace What:=Chr(160), Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False
' use paste special to make number strings actual numbers
CellToCopy = Cells(65536, 1).End(xlUp).Offset(1, 0).Address
Range(CellToCopy).Value = 1
Range(CellToCopy).Copy
Cells.SpecialCells(xlCellTypeConstants, 2).PasteSpecial Paste:=xlAll, Operation:=xlMultiply, SkipBlanks:= _
False, Transpose:=False
Range(CellToCopy).ClearContents
End Sub

 

[Ekim]

BJungheim/huh,

A easy way to determine the characters in a cell is to use Chip Pearson’s “Cell View” add-in. The add-in allows you to see the exact characters in a cell (including non-printable characters), as well as hex codes.
http://www.cpearson.com/excel/CellView.htm

When you install this add in, it will add an item to your View menu called "View Cell Contents". Select a cell then click the menu item to display a dialog box that shows the cell’s contents, together with the character codes.

Regards,

Mike