remove stuff after non breaking space in excel

[bill9898989]

Hello,

I have data I've copied off a webpage that I believe has non breaking spaces. When I copy it into an excel spreadsheet and try to use the left function, it returns #VALUE. Say the datum is in cell C1 as 33.45 368 - i.e. it looks like it has a space between the .45 and the 368. I can't seem to find a function to remove the space and the remaining numbers. Some other numbers have fewer digits as well like: 6.67 20. I'm trying to remove the space and everything after it and my formula: LEFT(C2,(FIND(" ",C2)-1)) will not work because I think it is a non-breaking space that needs to be converted so that EXCEL can see it? Does this make sense? I would like to be able to convert these to numbers so I can find the standard deviation and averages, etc... Any help is appreciated, thanks,

 

[JoeMo]

Here's some code from David McRitchie that generally does a good job of cleaning up imported data. Try it on a sample of your data. First, select the data with your mouse, then run the code.

Sub TrimALLMcRitchie()
   'David McRitchie 2000-07-03 mod 2002-08-16 2005-09-29 join.htm
   '-- http://www.mvps.org/dmcritchie/excel/join.htm#trimall
   ' - Optionally reenable improperly terminated Change Event macros
      Application.DisplayAlerts = True
      Application.EnableEvents = True   'should be part of Change Event macro
   If Application.Calculation = xlCalculationManual Then
      MsgBox "Calculation was OFF will be turned ON upon completion"
   End If
   
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   Dim cell As Range
   'Also Treat CHR 0160, as a space (CHR 032)
   Selection.Replace What:=Chr(160), replacement:=Chr(32), _
     lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   Selection.Replace What:=Chr(13) & Chr(10), replacement:=Chr(32), _
        lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   Selection.Replace What:=Chr(13), replacement:=Chr(32), _
        lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   Selection.Replace What:=Chr(21), replacement:=Chr(32), _
        lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   '---------------------------
   Selection.Replace What:=Chr(8), replacement:=Chr(32), _
      lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   Selection.Replace What:=Chr(9), replacement:=Chr(32), _
      lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
   'Trim in Excel removes extra internal spaces, VBA does not
   On Error Resume Next
   For Each cell In Intersect(Selection, _
      Selection.SpecialCells(xlConstants, xlTextValues))
     cell.Value = Application.Trim(cell.Value)
   Next cell
   On Error GoTo 0
   Application.Calculation = xlCalculationAutomatic
   Application.ScreenUpdating = True
End Sub

 

[bill9898989]

Ok, thanks, I'll give that a try, but it might be beyond my capabilities. Thanks for your help.

 

[JoeMo]

Ok, thanks, I'll give that a try, but it might be beyond my capabilities. Thanks for your help.

You are welcome. You need to install the code, then select the cells you want it to cleanup, then run the code. Here's how to install and run the code.

To install the code:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the code from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Press Alt+F8 keys to run the code
7. Make sure you have enabled macros whenever you open the file or the code will not run.

 

[Nolan_Tripp]

You could try FIND for the period character and adding 2. I also found a website (url below) that explains that a blank character is ASCII 160 where a blank space is ASCII 32. When I looked for the period it worked with ASCII 32 and 160 =LEFT(C2,(FIND(".",C2)+2)) but I also tried =LEFT(C2,(FIND(CHAR(160),C2)-1)) and it worked.

Removing Blank Characters or Nonbreaking Spaces from text | Dedicated Excel