Imported data spaces

[1234]

HI,

I copy data from a web, but from January the data comes with some
spaces that doesn´t let me calculate values. It comes with a space
after minus sign. Data like this:

- 13.27
- 100.00
- 76.50

And when I try to sum or average I can´t do it. How can I fix this?

Thanks!!

 

[Dave Peterson]

Select the range to fix.

Edit|replace (in xl2003 menus)
what: (space character)
with: (leave blank)
replace all

 

[1234]

Hi, Dave

I´ve tried to replace but it syas it doesn´t find any space. It´s
strange! If I go to edit mode in the cell I see a space between - and
numbers but if I try to find spaces it finds spaces in other columns
except in the one I want. What can I do?

Thanks so much for your time.

 

[1234]

I think it has something to do with CONCATENATE because when I select
the cell with the values in the name box appears the CONCATENATE
function.

Thanks!!

 

[Zaidy036]

I think it has something to do with CONCATENATE because when I select
the cell with the values in the name box appears the CONCATENATE
function.

Thanks!!

try:
1. Highlight the cells
2. Copy <Ctrl>-c
3. Paste Special - Values Only right over the same highlighted range

That will get rid of all formulas and then, if necessary, use Find "- "
and replace with "-"

Eric

 

[Dave Peterson]

I'm confused.

If you see the =concatenate() function in the formulabar--not the name box to
the left of the formularbar, you'll have to convert the formula to values:

select the range
Edit|copy
followed by:
edit|paste special|values

Then do the edit|replace stuff.

If the contents of the cell is really a value--and you see concatenate... in the
name box, then that won't matter.

But you'll have to find out what that character is. If you're importing the
values from a web page, it could be those HTML non-breaking spaces.

You may be able to use:
Select the range
edit|replace
what: alt-0160
(hit and hold the alt-key while you type 0160 on the number keypad--not the
numbers above the QWERTY keys)

Replace with: (leave blank)
replace all

If that doesn't work, you'll still want to find out what those characters are...

Saved from a previous post.

Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.aspx

Depending on what that character is, you may be able to use alt-#### (from the
number keypad) to enter the character into the Other box in the text to columns
wizard dialog.

In fact, you may be able to select the character (in the formula bar), and copy
it. Then use ctrl-v to paste into that text to columns Other box.

You may be able to use Edit|Replace to change the character--Some characters can
be entered by holding the alt-key and typing the hex number on the numeric
keypad. For example, alt-0010 (or ctrl-j) can be used for linefeeds. But I've
never been able to get alt-0013 to work for carriage returns.

Another alternative is to fix it via a formula:

=substitute(a1,char(##),"")

Replace ## with the ASCII value you see in Chip's addin.

Or you could use a macro (after using Chip's CellView addin):

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(##)) '<--What showed up in CellView?

myGoodChars = Array("")

If UBound(myGoodChars) <> UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)