How to convert text with middle blank space to a number in Excel?
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.
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
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: