Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-03-2023, 09:37 AM
Haha88 Haha88 is offline Convert text to number help Windows 8 Convert text to number help Office 2010 32bit
Advanced Beginner
Convert text to number help
 
Join Date: Mar 2015
Posts: 75
Haha88 is on a distinguished road
Default 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,
Attached Files
File Type: xlsx Convert text to number form.xlsx (9.3 KB, 7 views)
Reply With Quote
  #2  
Old 01-03-2023, 01:54 PM
kilroyscarnival kilroyscarnival is offline Convert text to number help Windows 10 Convert text to number help Office 2021
Expert
 
Join Date: May 2019
Posts: 344
kilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nice
Default

Quote:
Originally Posted by Haha88 View Post
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,
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.
Reply With Quote
  #3  
Old 01-04-2023, 01:34 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Convert text to number help Windows 10 Convert text to number help Office 2021
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

With US regional settings
Code:
=SUBSTITUTE(D5,"$","")+0
With European regional settings
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
Reply With Quote
  #4  
Old 01-04-2023, 02:43 AM
ArviLaanemets ArviLaanemets is offline Convert text to number help Windows 8 Convert text to number help Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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;"$";"");".";",")
, and copy it down.

Edit: I see Pecoflyer adviced practically same!
Reply With Quote
  #5  
Old 01-04-2023, 02:52 AM
macropod's Avatar
macropod macropod is offline Convert text to number help Windows 10 Convert text to number help Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #6  
Old 01-04-2023, 04:09 AM
azka azka is offline Convert text to number help Windows 10 Convert text to number help Office 2016
Novice
 
Join Date: Jan 2023
Posts: 1
azka is on a distinguished road
Default

Another usual way, You can replace "$" with nothing by using CTRL H.
Reply With Quote
  #7  
Old 01-04-2023, 05:32 AM
Haha88 Haha88 is offline Convert text to number help Windows 8 Convert text to number help Office 2010 32bit
Advanced Beginner
Convert text to number help
 
Join Date: Mar 2015
Posts: 75
Haha88 is on a distinguished road
Default

Thank you for looking into this and your quick response. Much appreciated.
Reply With Quote
  #8  
Old 01-04-2023, 05:37 AM
Haha88 Haha88 is offline Convert text to number help Windows 8 Convert text to number help Office 2010 32bit
Advanced Beginner
Convert text to number help
 
Join Date: Mar 2015
Posts: 75
Haha88 is on a distinguished road
Default

Quote:
Originally Posted by Pecoflyer View Post
With US regional settings
Code:
=SUBSTITUTE(D5,"$","")+0
With European regional settings
Code:
=SUBSTITUTE(SUBSTITUTE(D5;"$";"");".";",")+0
Thank you for your help. Substitute works brilliantly.
Reply With Quote
  #9  
Old 01-04-2023, 05:39 AM
Haha88 Haha88 is offline Convert text to number help Windows 8 Convert text to number help Office 2010 32bit
Advanced Beginner
Convert text to number help
 
Join Date: Mar 2015
Posts: 75
Haha88 is on a distinguished road
Default

Quote:
Originally Posted by azka View Post
Another usual way, You can replace "$" with nothing by using CTRL H.
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!
Reply With Quote
  #10  
Old 01-04-2023, 05:42 AM
Haha88 Haha88 is offline Convert text to number help Windows 8 Convert text to number help Office 2010 32bit
Advanced Beginner
Convert text to number help
 
Join Date: Mar 2015
Posts: 75
Haha88 is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
Why use VBA at all, when you use a simple formula on each row:
=VALUE(D5)
with whatever regional cell formatting you require?
Thank you for your help. For some reason, value formula doesn't work as it returned as#value! error.
Reply With Quote
  #11  
Old 01-04-2023, 05:43 AM
Haha88 Haha88 is offline Convert text to number help Windows 8 Convert text to number help Office 2010 32bit
Advanced Beginner
Convert text to number help
 
Join Date: Mar 2015
Posts: 75
Haha88 is on a distinguished road
Default

Quote:
Originally Posted by Pecoflyer View Post
With US regional settings
Code:
=SUBSTITUTE(D5,"$","")+0
With European regional settings
Code:
=SUBSTITUTE(SUBSTITUTE(D5;"$";"");".";",")+0
Thank you for such detail breakdown. It worked wonderfully!
Reply With Quote
  #12  
Old 01-04-2023, 05:45 AM
Haha88 Haha88 is offline Convert text to number help Windows 8 Convert text to number help Office 2010 32bit
Advanced Beginner
Convert text to number help
 
Join Date: Mar 2015
Posts: 75
Haha88 is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
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;"$";"");".";",")
, and copy it down.

Edit: I see Pecoflyer adviced practically same!
Thank you for looking into this for me. Much appreciated.
Reply With Quote
  #13  
Old 01-04-2023, 04:42 PM
macropod's Avatar
macropod macropod is offline Convert text to number help Windows 10 Convert text to number help Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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]
Reply With Quote
  #14  
Old 01-04-2023, 11:28 PM
ArviLaanemets ArviLaanemets is offline Convert text to number help Windows 8 Convert text to number help Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Quote:
Originally Posted by macropod View Post
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.
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)!
Reply With Quote
  #15  
Old 01-04-2023, 11:35 PM
macropod's Avatar
macropod macropod is offline Convert text to number help Windows 10 Convert text to number help Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

It worked for me just fine with the OP's worksheet...
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


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

Other Forums: Access Forums

All times are GMT -7. The time now is 12:04 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft