0

I really tried a LOT with in-built functions and also with google search but none of the ways doesn't worked out for expected result.

My exact problem is: I've few numeric columns which i got from a website and copied directly into excel.

In those columns there is a SINGLE Leading space at the beginning of each number in the cell of the entire column. Example 523946.00. In this number there is a single space before the digit 5.

I tried a lot with TRIM and SUBSTITUTE in-built functions but nothing able to resolve my problem of removing spaces.

And also my expectation is when i select two or multiple cells in the same column(spaces removed) then automatically Excel should show or display the AVERAGE: <Average value> SUM: <total Sum> COUNT: <count value> at the below status bar or bottom ribbon.

Say, AVERAGE: 175.49 COUNT: 2 SUM: 350.98

This type of information is not showing at the bottom. Only i'm able to see COUNT: 2 alone....why? I want in General Format only. No any special formats.

I'm using MS Excel 2013

venkat
  • 5,648
  • 16
  • 58
  • 83
  • Trim Should have worked. What happened when you tried trim? – user2140261 Apr 17 '13 at 12:44
  • Trim is not working in removing Single Space alone. – venkat Apr 17 '13 at 12:50
  • So you have multiple spaces but you want to remove 1? – user2140261 Apr 17 '13 at 13:01
  • See my comment in http://stackoverflow.com/questions/9578397/how-to-remove-leading-or-trailing-spaces-in-an-entire-column-of-excel-worksheet/9582919#9582919 - it is probably the CHAR(160) issue – brettdj Apr 17 '13 at 13:08
  • @sukumar You are the author of this earlier identical thread ... http://stackoverflow.com/questions/9578397/how-to-remove-leading-or-trailing-spaces-in-an-entire-column-of-excel-worksheet/9582919#9582919 – brettdj Apr 17 '13 at 13:10
  • @brettdj I actually supplied that same code in my answer here. But, I'm pretty confident that the fact he's using numbers, should say he can just format the column and not have to worry about any replacing in the future or maintaining the formula if something breaks. The last questions seems to have extremly over complicated answers. – user2140261 Apr 17 '13 at 13:13
  • No, you didn't mention CHAR(160) – brettdj Apr 17 '13 at 13:14
  • So that he can do a find and replace? That would be foolish, if he is importing data and doing this hourly or even daily then that would be a large amount of time spent simply finding and replacing a value. At the very least you could supply a vba on change event to handle that. Finding and replacing every time seems more like a work around then a solution. – user2140261 Apr 17 '13 at 13:17
  • This question is a little tricky with my expectation result and different to my earlier question – venkat Apr 17 '13 at 13:18
  • Hi, With the given possible solutions using Last one now i'm able to get the data without spaces but my expectation is still not met. So when i select two or multiple cells then automatically Excel should show or display the **AVERAGE=** **SUM=** **COUNT=** at the below status bar or bottom ribbon. This information is not showing. Only i'm getting `COUNT` alone....why? I want in General Format only. No any special formats. – venkat Apr 17 '13 at 13:21
  • 3
    This artikle actually explains the problem and the solution. read it carefully. http://office.microsoft.com/en-001/excel-help/remove-spaces-from-the-beginning-and-end-of-a-cell-HP003056131.aspx remember to use VALUE() around these functions if you wan to do calculations on the value – Ole Henrik Skogstrøm Apr 17 '13 at 14:08
  • 1
    Thanks Ole Henrik. Thank U :) – venkat Apr 17 '13 at 14:14

5 Answers5

3

Edit:

You can actually just use find and replace.

  1. Copy one of the trouble cells.
  2. Select all the cells containing non break space, and select find and replace.
  3. Paste the copied cell into the find bar, delete everything but the last character (asuming that is the non breaking space).
  4. Leave the replace bar empty and press replace all.

This removes all non breaking spaces. :)

**Old Solution:**You can add nothing with paste special to the whole column where the spaces occur.

  1. First copy an completely empty cell. (! remember this step)

  2. Then select all cells in the column and right click and select paste special.

  3. Then select "add" almost at the bottom (see picture) and press ok.

enter image description here

This will make excel reevaluate the values as if you had modified in and entered the value manually. Excel then correctly converts them to numbers. :)

Ole Henrik Skogstrøm
  • 6,353
  • 10
  • 57
  • 89
  • This doesn't get rid of the non breaking spaces though. – user2140261 Apr 17 '13 at 13:54
  • You might be able to get them by copying the nonbreaking space and using a simple find and replace. on the entire column or sheet. (to copy the nb space, copy a cell into the find window and delete all numbers but noting more, leaving the non breaking space) – Ole Henrik Skogstrøm Apr 17 '13 at 14:00
  • In a single use situation I would go this route, but if data is constantly being imported, this would really become a hassle. Although i'd like to get speed results on a vba on change event with this automated vs adding a column with my forumla. – user2140261 Apr 17 '13 at 14:18
  • If you organize your data into a table it will automatically expand formulas for every added row automating this process without VBA :) – Ole Henrik Skogstrøm Apr 17 '13 at 14:19
  • But your not using a formula, you'd have to do find and replace and special paste everytime you get new data. – user2140261 Apr 17 '13 at 14:24
  • Ah yeas, sorry the last comment was about the VALUE function and all the other functions in the comment on your question. – Ole Henrik Skogstrøm Apr 17 '13 at 15:03
2

First make sure you have the column Formatted as you would like. Make sure it is a number with 2 decimal places (or how ever many you need), then also make sure that there is no Indents (Maybe you think the Indent is a space?) And that you have it Aligned to the Left, Or where you want the Data To be. This alone should take care of your issue.

If that doesn't work here a list of possible solutions.

=Value(Trim(A1)) ' Removes all white space before and after the text in A1

=Value(Clean(A1)) 'Removes all non printable Charactersin A1

=Value(SUBSTITUTE(I3," ","")) 'Substitutes(Replaces) all instances of " "(Space) with ""(nothing)
                       '****Note: With Substitute you can also specify how many
                       ' Substitutes(Replaces) to make of the value

=Value(SUBSTITUTE(I3," ","",1)) ' Same as above but with only remove the FIRST space

 =Value(Trim(Clean(A1)))    ' Removes all white space before and after the text 
                    ' after removing all Non-Printable Characters

=Value(Trim(Clean(Substitute(A1," ","")))) ' Removes all white space before and after the 
                                    'after removing all Non-Printable Characters
                                    ' And after replaceing all spaces with nothing 

=Value(Right(A1, Len(A1)-1))  ' This takes the End of your text by the number of characters  
                     ' in the value Except the First (In your case should be the Space)


=Value(SUBSTITUTE(I6,CHAR(160),"")) 'To help with the non breaking spaces also.

If nothing works could you please share Why you would like to remove the space? As in what you are trying to do with the data? As maybe that will open more solutions

With Ole Henrik Skogstrøm's Suggestion added Value around the functions to get the result as a value.

user2140261
  • 7,855
  • 7
  • 32
  • 45
  • Hi, With the above possible solutions using Last one now i'm able to get the data without spaces but my expectation is when i select two or multiple cells then automatically Excel should show or display the **AVERAGE=** **SUM=** **COUNT=** at the below status bar or bottom ribbon. This information is not showing. Only i'm getting `COUNT` alone....why? I want in General Format only. No any special formats. – venkat Apr 17 '13 at 13:15
  • I updated my question with my expectation outcome or result as well. Please check. – venkat Apr 17 '13 at 13:37
  • Please ensure this question is still in Open status from `Close(1)`. – venkat Apr 17 '13 at 13:39
  • One of these functions above should work. however you might want to use VALUE(...) around all of these expressions to convert it to a number in excel. Otherwise it is still considered text since most text functions return text. – Ole Henrik Skogstrøm Apr 17 '13 at 13:57
  • 2
    Vow! Just adding the `VALUE` function as prefix itself got the resultant which i expected result. Thanks. Gr8 Work!! – venkat Apr 17 '13 at 14:12
  • 1
    Please see my edited answer if you need a quick solution without the use of additional columns or formulas. :) – Ole Henrik Skogstrøm Apr 17 '13 at 14:15
  • Got a doubt. Can't i apply the above space removal possible solutions directly to the existing own SELF column where data is actually present. – venkat Apr 17 '13 at 14:29
  • That would be a circulatory Reference. The only way to do that is with VBA – user2140261 Apr 17 '13 at 14:32
0

It may be Excel is treating your cells as text data. Is the fun Green Triangle present?

This is a common problem in excel. Forcing numeric is easier than text. Just format a column as number or General and then put in the function value() into the cells.

Things can be cleaned up from there with Copy/Paste Special values and then remove the original column.

Alan Waage
  • 603
  • 4
  • 12
0

Using Excel 2007 brettdj answered this for me. This is what worked for me and it was SIMPLE!!

To remove the CHAR(160) directly without a workaround formula go to Find & Replace

in the Find What hold ALT and type 0160 using the numeric keypad then Leave Replace With as blank and select Replace All

motherchook
  • 1
  • 1
0

In your case, since you always have a string of numbers, where you want to remove just the first character (a space), this formula should work:

=RIGHT(A1,LEN(A1)-1)

The numbers can vary in length, important is only, that you want to remove just 1, (or 2, or 3 etc) characters. It will still work. {If you had 2 empty spaces in front, then you would use in the formula -2, if three -3, etc)

However, if you had always a different amount of blanks in your string of numbers, like I had, you could use this formula, which worked for me:

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

,assuming that the issue you are facing is code "160". That you can find by typing: =code(A1), which in my case gave me the result "160". Therefore char(160) in the formula above.

radogruber
  • 1
  • 1