如何在Excel 2013中删除数字列中的单个前导空格 [英] How to remove a single Leading space in the numeric column in Excel 2013

问题描述

我真的尝试了一个内置功能的LOT，还有谷歌搜索，但没有一种方法不能预期的结果。

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.

我的确切问题是：
我有一些数字列，我从一个网站获得并直接复制到excel。

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

在这些列中有一个SINGLE领先的空间整个列的单元格中的每个数字的开始。例如 523946.00 。在这个数字中，数字 5 之前有一个空格。

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.

我尝试了很多TRIM和SUBSTITUTE建立的功能，但没有什么能够解决我的删除空格的问题。

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

而且我的期望是当我选择两个或多个单元格相同列（已移除空格），则Excel将自动显示或显示 AVERAGE：< Average value> SUM：< total Sum> COUNT：< count value> 在以下状态栏或底部功能区。

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.

说， AVERAGE：175.49 COUNT：2 SUM：350.98

这种类型的信息没有显示在底部。只有我能够看到 COUNT：2 单独....为什么？
我只想在一般格式。没有任何特殊格式。

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.

我正在使用MS Excel 2013

I'm using MS Excel 2013

推荐答案

p>首先确保你的列格式化为你想要的。确保它是一个有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

与Ole HenrikSkogstrøm的建议相关的功能增加了价值，以获得结果作为一个价值。

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

这篇关于如何在Excel 2013中删除数字列中的单个前导空格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！