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

3

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
    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. – Jörg W Mittag May 10 '16 at 8:45
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).

  • Isn't working either – vanessen May 10 '16 at 6:25
  • Updated the post with image to show the error obtained – vanessen May 10 '16 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 '16 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 '16 at 7:02
  • #value! means not recognizing the blank between numbers as blank space – Sam May 10 '16 at 8:06
2

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
  • I think your approach is better than mine...........it is better to understand the issue rather than blindly avoid it. – Gary's Student May 11 '16 at 11:06
  • It is, but it's also a lot easier to use a function than a macro, for most users. – Raystafarian May 11 '16 at 11:16
2

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

  • Good stuff there – Raystafarian May 11 '16 at 10:47
  • @Raystafarian .......................thanks................ – Gary's Student May 11 '16 at 10:50
1

Replace the spaces using the SUBSTITUTE function:

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

0
=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.

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.

  • It says cannot find a match – vanessen May 10 '16 at 9:58
  • @vanessen what version of excel are you using? – madalinivascu May 10 '16 at 10:00
  • 1
    @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 '16 at 10:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.