#1
|
|||
|
|||
Convert text to number help
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, |
#2
|
|||
|
|||
Quote:
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. |
#3
|
||||
|
||||
With US regional settings
Code:
=SUBSTITUTE(D5,"$","")+0 Code:
=SUBSTITUTE(SUBSTITUTE(D5;"$";"");".";",")+0
__________________
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 |
#4
|
|||
|
|||
As you want comma as decimal delimiter, I assume you use some European regional settings. So use in row 5 the formula
Code:
=1*SUBSTITUTE(SUBSTITUTE(D5;"$";"");".";",") Edit: I see Pecoflyer adviced practically same! |
#5
|
||||
|
||||
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] |
#6
|
|||
|
|||
Another usual way, You can replace "$" with nothing by using CTRL H.
|
#7
|
|||
|
|||
Thank you for looking into this and your quick response. Much appreciated.
|
#8
|
|||
|
|||
Thank you for your help. Substitute works brilliantly.
|
#9
|
|||
|
|||
Quote:
Much appreciated! |
#10
|
|||
|
|||
Thank you for your help. For some reason, value formula doesn't work as it returned as#value! error.
|
#11
|
|||
|
|||
Thank you for such detail breakdown. It worked wonderfully!
|
#12
|
|||
|
|||
Thank you for looking into this for me. Much appreciated.
|
#13
|
||||
|
||||
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] |
#14
|
|||
|
|||
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)!
|
#15
|
||||
|
||||
It worked for me just fine with the OP's worksheet...
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Always convert text to number? | SoMany | Excel | 1 | 04-26-2021 02:09 AM |
Convert text to phone number in merge | ChrisComp | Mail Merge | 1 | 03-03-2020 10:39 AM |
Reliable way to convert text to number with a formula | levraininjaneer | Excel | 8 | 12-22-2019 04:47 PM |
Attendance table Text Value Convert to Number | NickFazer | Excel | 2 | 11-08-2018 01:29 AM |
Convert Number to Text | devcon | Word | 0 | 07-10-2010 01:16 AM |