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

microsoft excelworksheet-function

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.

Best Answer

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.

Related Solutions

Excel – Does excel have an arrow key shortcut to skip blank cells

No, but set it up this way, and Tab and Shift+Tab will take you to the next and previous highlighted row. First do this:

Change your formula to =IF(MID($C2,5,1)=" ", 1, "") (copying down, of course)
Highlight the column (Ctrl+Space will do that if you're in any cell in that column).
Press F5, then Alt+Special, Formula, de-select Text, then Enter
- To exclude errors, de-select Errors in step 3 also. (e.g., when LEN($C2)<5, but you could also change the formula to account for that)

Now the Tab key will step you through each cell in that column that has a numeric value in it (1). It also has the added bonus that in step 2 above, you can see the total of the highlighted rows in the status bar at the bottom of the screen (Sum:).

Excel VBA or Formula to convert text to Date with Time type

Select the cells and run:

Sub DateFixer()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
        For Each r In Selection
            v = r.Text
            r.Clear
            r.NumberFormat = "dd/mm/yyyy hh:mm:ss"
            r.Value = DateSerial(Mid(v, 7, 4), Mid(v, 4, 2), Left(v, 2)) + TimeSerial(Mid(v, 12, 2), Mid(v, 15, 2), Right(v, 2))
        Next r
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
End Sub

Best Answer

Related Solutions

Excel – Does excel have an arrow key shortcut to skip blank cells

Excel VBA or Formula to convert text to Date with Time type

Related Question