Calculating with numbers saved as text?

Jaymond Flurrie

Well-known Member
Joined
Sep 22, 2008
Messages
919
Office Version
  1. 365
Platform
  1. Windows
Is it possible to calculate with numbers saved as text? Like for example a SUMIF-function so that the numbers to sum are saved as text?

Or is the only option to convert the text to numbers first, and then to calculate?
 
Last edited:

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
To add on this, the numbers are nothing but numbers, so they are not like numbers and text. They are just saved as text. Besides of SUMIF, VLOOKUP would also help.
 
Upvote 0
Hi,

Using Value() will convert text to numbers in a formula. e.g. =Value(A1)+Value(A2)

Hope this helps,

Eric
 
Last edited:
Upvote 0
Hi,

Using Value() will convert text to numbers in a formula. e.g. =Value(A1)+Value(A2)

Hope this helps,

Eric

Doesn't seem to work like this:
=SUMIFS(value($G:$G);value($E:$E);"3040")
:/

edit. ; is the parameter separator in my locale.
 
Last edited:
Upvote 0
For example, instead of
=SUMIF(B1:B6,"a",D1:D6)
try
=SUMPRODUCT(--(B1:B6="a"),--(D1:D6))

Avoid whole column references where possible.
 
Last edited:
Upvote 0
Don't forget the Evaluate function.

Code:
Debug.Print Evaluate("2+2") '=4


Dim S1 As String, S2 As String
S1 = "3"
S2 = "07"
Debug.Print Evaluate(S1 & "+" & S2) '=10
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top