How to convert text with middle blank space to a number in Excel?

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.

vanessen

Posted 2016-05-10T06:14:37.903

Reputation: 143

Answers

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.

06chakin

Posted 2016-05-10T06:14:37.903

Reputation: 56

2If 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 – 2016-05-10T08:45:29.420

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

Raystafarian

Posted 2016-05-10T06:14:37.903

Reputation: 20 384

I think your approach is better than mine...........it is better to understand the issue rather than blindly avoid it. – Gary's Student – 2016-05-11T11:06:55.407

It is, but it's also a lot easier to use a function than a macro, for most users. – Raystafarian – 2016-05-11T11:16:01.273

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:

Gary's Student

Posted 2016-05-10T06:14:37.903

Reputation: 15 540

Good stuff there – Raystafarian – 2016-05-11T10:47:53.050

@Raystafarian .......................thanks................ – Gary's Student – 2016-05-11T10:50:05.037

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).

Sam

Posted 2016-05-10T06:14:37.903

Reputation: 888

Isn't working either – vanessen – 2016-05-10T06:25:58.950

Updated the post with image to show the error obtained – vanessen – 2016-05-10T06:31:44.297

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 – 2016-05-10T06:41:42.877

The problem is that the sustititute method is not recognising blank " ". If i try replacing another text it works :( – vanessen – 2016-05-10T07:02:55.803

#value! means not recognizing the blank between numbers as blank space – Sam – 2016-05-10T08:06:48.457

I have updated the formula in case the blank is line feed or other – Sam – 2016-05-10T08:52:39.270

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

jingyu9575

Posted 2016-05-10T06:14:37.903

Reputation: 926

Replace the spaces using the SUBSTITUTE function:

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

Atzmon

Posted 2016-05-10T06:14:37.903

Reputation: 2 639

@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 – 2019-09-23T20:55:09.567

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 – 2019-09-23T21:01:51.250

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.

madalinivascu

Posted 2016-05-10T06:14:37.903

Reputation: 101

It says cannot find a match – vanessen – 2016-05-10T09:58:07.693

@vanessen what version of excel are you using? – madalinivascu – 2016-05-10T10:00:48.687

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 – 2016-05-10T10:01:07.163