Removing Extra Space in Copied Data

[Jakila2]

I copied a bunch of economic data and I am trying to do calculations with it. Only problem is that the original source contained extra spaces, so Excel doesn't recognize the data as numbers.

For instance, instead of "9.9", it would be "9.9 " with the extra space. All the data in my spreadsheet is like this.

Is there any way to correct this all at once, without manually deleting spaces in the spreadsheet?

[jeffreybrown]

In a helper column you could use =Trim(A1)

[Jakila2]

In a helper column you could use =Trim(A1)

TRIM doesn't seem to work for whatever reason. Not sure if there's something odd about the data.

I also tried VALUE and that didn't seem to work.

[jeffreybrown]

Okay, maybe try...

=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))

http://www.rondebruin.nl/clean.htm

[ChemistB]

There is some sort of other invisible character there. Things to try
=CLEAN(A1)

or =CODE(RIGHT(A1,1)) that will tell us what invisible character it is (i.e. 32 = space)

Then
=(SUBSTITUTE(A1,CHAR(32),""))+0
Or
Do a search replace by copying that invisibile character into the replace field and replace it with nothing.
Any of these work for you?

[Jakila2]

Wow, this is one annoying set of data!

Both of the below failed to work:

=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))
=CLEAN(A1)

The third one:

=CODE(RIGHT(A1,1))

Gives me a result of "160". What does that mean?


I also tried the replacing method earlier and couldn't get it to work.

I'm not sure what is up with this data.

[jeffreybrown]

Do you have a small sample workbook you can attach?

[Jakila2]

Here's a spreadsheet with one page of the data.


Note that the only way I've been able to fix it thus far is to manually delete the space in every cell. I'm not sure why that would work, but none of the formulas would work to do the same thing.

[Jakila2]

Actually, looks like I might have changed the data slightly in that spreadsheet. I experimented with custom formatting a bit.

This edit eliminates that. (Though, it shouldn't alter things significantly anyway).

[jeffreybrown]

This worked for me...

=TRIM(CLEAN(SUBSTITUTE(B3,CHAR(160)," ")))

Check out the attachment. I put a column for before which shows a length of 4 and then after the formula, a length of 3.

[martindwilson]

you have char 160 at the end of each cell
select all then find replace alt+0160 with nothing
nb you must use the num keypad to enter 0160
here it is done

[Jakila2]

Thanks a bunch, Jeff.

Actually, looks like it worked the first time. I only thought it didn't because the alignment was exactly the same on mine. When I delete the space manually, it aligns to the right.

Sorry about that. My mistake there.

[jeffreybrown]

Not a problem...just glad you got it working