4

I am not able to convert a text value of 5 205 with a middle blank space into 5205 in Excel. If I leave the value like this, my report chart with sum displays 0.

If I try to convert the text using the following formula:

  VALUE(TRIM(B297))

it returns an error.

Even if I convert the cell to a number, it leaves the value as it is. My sum still returns 0.

=VALUE(SUBSTITUTE(B297," ","")) isn't working either.

Screenshot showing the result #VALUE

9 Answers 9

4

The character that is creating the separation (originally referred to as a "space") may not be a space at all.

Try - using your mouse to select just the blank 'character' and paste it between the "" marks in the substitute formula.

or try one of these three formulas

=SUBSTITUTE(A1,CHAR(10),"")

=SUBSTITUTE(A1,CHAR(9),"")

=SUBSTITUTE(A1,CHAR(13),"")

What do they do? Those are character codes for various types of line feeds / carriage returns.

Give it a try and report back.

1
  • 2
    If the document the number is coming from is correctly typeset, it would most likely be a U+202F narrow no-break space, or possibly a U+2009 thin space or U+00A0 no-break space. Somewhat less likely, it may also be a U+2007 figure space. May 10, 2016 at 8:45
3

Since the answer was "it's not a space" - if you want to know what all the characters are in a cell, this macro will print them for you and you can compare them to their ascii values

Sub WhatIsThat()
    Dim testCell As Range
    Dim testString As String
    Dim i As Long

    Set testCell = Range("A1")
    testString = testCell.Value

    For i = 1 To Len(testString)
        Debug.Print Asc(Mid(testString, i, 1))
    Next

End Sub
2
  • I think your approach is better than mine...........it is better to understand the issue rather than blindly avoid it. May 11, 2016 at 11:06
  • It is, but it's also a lot easier to use a function than a macro, for most users. May 11, 2016 at 11:16
3

Say we have a string of less than 256 characters, some of which are numbers and others are not, this should get the numbers. It does not depend on knowing which characters are non-numbers or knowing what the non-numbers are.

With the text string in cell A1:

=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$256),1))*ROW($1:$256),0),ROW($1:$256))+1,1)*10^ROW($1:$256)/10)

Some examples:

enter image description here

2
  • Good stuff there May 11, 2016 at 10:47
  • @Raystafarian .......................thanks................ May 11, 2016 at 10:50
2

enter image description here

To substitute the blank " " in A2 to "" empty, write the formula below:

=VALUE(SUBSTITUTE(A2," ",""))

Copy all results and paste special -> values in a new column to manipulate them easily.

Update: try this formula:

=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B297,CHAR(10),""),CHAR(9),""),CHAR(13),"")," ",""))

It removes not only spaces, but also other blank characters (HT, LF and CR).

6
  • Isn't working either
    – vanessen
    May 10, 2016 at 6:25
  • Updated the post with image to show the error obtained
    – vanessen
    May 10, 2016 at 6:31
  • No its not this, the function uses , and not ; It looks like the sustitute function cannot replace the white space thus the value formula dies
    – vanessen
    May 10, 2016 at 6:41
  • The problem is that the sustititute method is not recognising blank " ". If i try replacing another text it works :(
    – vanessen
    May 10, 2016 at 7:02
  • #value! means not recognizing the blank between numbers as blank space
    – Sam
    May 10, 2016 at 8:06
1
=VALUE(SUBSTITUTE(B297,"$",""))

Then double click B297, select the thing between 5 and 205, copy it and replace the $ in the formula with it. The space changes to U+00A0 when I copy it into Excel.

1

An answer for more recent times, in that it uses TEXTJOIN() and SEQUENCE() although what each does had its methods when the problem was asked.

The main value though, is that this will solve ANYTHING that is caused by the MANY strange characters webiste developers use for formatting their material, and whatever the source here was, this formula will STILL leave only numerals AND the "-" and "." characters though it should be obvious how to change that to allow other characters or not allow these:

=VALUE(TEXTJOIN("",TRUE,IFERROR(UNICHAR(((UNICODE(MID(A1,SEQUENCE(1,LEN(A1)),1))<>47)*(UNICODE(MID(A1,SEQUENCE(1,LEN(A1)),1))>44)*(UNICODE(MID(A1,SEQUENCE(1,LEN(A1)),1))<58)*1)*UNICODE(MID(A1,SEQUENCE(1,LEN(A1)),1))),"")))

Further, if one wants the displayed formatting with the result, one may use the following format:

0 ##0

which will force a "0" to display even if the resulting number leads with a zero that Excel would like to drop. I did not make place for a decimal divider of some sort or decimal values as the current display suggests the shown space IS in place of a decimal divider, though that might be way off-base.

As put though, the relationships should be equivalent so the formula should be completely chartable for you as well. (5,205 is to 5.205 as... so the chart should look fine.)

It also gets past the irksome failure of Excel to allow the F2-Edit-remove all characters between the numerals-Enter technique when there are things to delete and the Paste|Special|Multiply (by 1) technique when they are all gone. (Really, the VBA select.value = select.value can force it in these cases (where these should work because it's all cleaned up, but just... don't...), but Excel just refuses to let in-cell or cell editing to do it.) Since this extracts only what it considers valid, leaving behind all odd characters AND Excel's hidden formatting characters and other trash, that doesn't arise as something to overcome.

0

Replace the spaces using the SUBSTITUTE function:

=VALUE(SUBSTITUTE(A2," ",""))

2
  • @AlexM Check the edit history of the question. The original version did not mention this. It was added ten minutes after I posted my answer.
    – Atzmon
    Sep 23, 2019 at 20:55
  • My fault. Somehow I read May 10 '16 at 14:06 correctly as 2pm but misread answered May 10 '16 at 6:19 as 6pm. My vote's locked at this point but if you edit your A I'll change it...
    – Alex M
    Sep 23, 2019 at 21:01
0

Select the cells you wish to remove the white space from. Press Ctrl+F, go to find and replace, place a space (or any character you wish to replace) in the find input and nothing on the replace input, push the replace all button, done.

3
  • It says cannot find a match
    – vanessen
    May 10, 2016 at 9:58
  • @vanessen what version of excel are you using? May 10, 2016 at 10:00
  • 2
    @06chakin answer is the one. Apparently it was not a blank char. I selected it using my mouse then replace between the quote in the formula, it replaces it well :)
    – vanessen
    May 10, 2016 at 10:01
0

The following approach simply texts characters for VALUE() and keeps only those that pass the test (and hence are numerals). It then strings them together and uses VALUE() again, this time to change them from string to number:

=VALUE(TEXTJOIN("",TRUE,IFERROR(VALUE(MID(A1,SEQUENCE(1,LEN(A1)),1)),"")))

This is 2021 so I used TEXTJOIN() and SEQUENCE() but one could use CONCAT() and the old "ROW(1:xxx)" trick instead for a more 2016-y feel.

You'd also have to use {CSE}, I suppose, but that's no trick.

This way the return is simply the digits present, as the question asks for. It does, and learns, nothing else.

Since no other characters CAN be kept, it doesn't matter whether they are bizarre, non-printing blank characters or not, and so it doesn't matter either, just what they are. Nothing else will be kept and that's ok as nothing else was desired.

A last peer at the data makes me wonder if row 296 in the pic has a decimal point. If so, one could keep it using the following (with CONCAT() in place of TEXTJOIN() just to show it works nicely):

=VALUE(CONCAT(IF(MID(A1,SEQUENCE(1,LEN(A1)),1)=".",MID(A1,SEQUENCE(1,LEN(A1)),1),IFERROR(VALUE(MID(A1,SEQUENCE(1,LEN(A1)),1)),""))))

One is testing characters for the match to a decimal point THEN performing the check for numeralcy. Order is important, and other tests would go first as well.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .