Hi,



I would like some help creating a formula where I can extract the number from the cell and convert it to number form so sum can be added.

I attached the data and the expect result.

Many thanks,

Attached Files

Convert text to number form.xlsx (9.3 KB, 9 views)

You can do this a bunch of different ways.

Immediately to the right of your column of numbers stored as text, type =VALUE(E5_ and copy down. Then select those cells and paste them back as values.

I used to keep a dirt-simple macro to do a whole sheet of numbers (TV ratings) stored as text, which simply replaced every digit 0 through 9 with itself. Doing that refreshed every cell with numbers in it, and would allow it to take number formatting, be summed, etc.

Sub replnos()
'
' replnos Macro
' Replace 0-9 with themselves to convert numbers stored as text to actual calculable numbers - Ann's cheat
'
' Cells.Replace What:="0", Replacement:="0", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="1", Replacement:="1", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="2", Replacement:="2", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="3", Replacement:="3", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="4", Replacement:="4", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="5", Replacement:="5", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="6", Replacement:="6", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="7", Replacement:="7", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="8", Replacement:="8", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="9", Replacement:="9", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

You can also highlight the cells and use Data/Text to Columns, and without changing delimited/fixed width, just hit Finish. This always made me too nervous with consecutive columns of data, I was afraid it would parse on something like a space and cover up the next column of stuff, so I just used my cheat macro as I was certain it would stay in the same cell.

With US regional settings With European regional settings

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

As you want comma as decimal delimiter, I assume you use some European regional settings. So use in row 5 the formula
, and copy it down.

Edit: I see Pecoflyer adviced practically same!

Why use VBA at all, when you use a simple formula on each row:
=VALUE(D5)
with whatever regional cell formatting you require?

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Another usual way, You can replace "$" with nothing by using CTRL H.

Thank you for looking into this and your quick response. Much appreciated.

Thank you for your help. Substitute works brilliantly.

Thank you so much for this. Don't know why I didn't think of this until now as I used this so often for other tasks.

Much appreciated!

Thank you for your help. For some reason, value formula doesn't work as it returned as#value! error.

Thank you for such detail breakdown. It worked wonderfully!

Thank you for looking into this for me. Much appreciated.

Another simple solution that requires no formula is to:
1. Type 1 into any cell
2. Copy/Cut the number to the clipboard
3. Select the range to convert
4. Use Paste Special>Values>Multiply.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

This doesn't work, when the string isn't alfanumeric string for user's current regional settings (in OP's case, I suspect not either "$", nor "." don't allow this conversion to work)!

It worked for me just fine with the OP's worksheet...

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]