Convert Numbers Text into Numbers

pto160

Well-known Member
Joined
Feb 1, 2009
Messages
505
Office Version
  1. 365
Platform
  1. Windows
How can I convert a number that is text into a number? The decimal number could have one or two spaces after the whole number. For example 123 10. I would like 123.10. Text that has 0 decimal points like 456 would be 456.00. I think once I can convert the text to numbers I can then format the cell.

Book1
AB
1TextWhat I like
2456 23456.23
3789 57789.57
410000 2310,000.23
5812812.00
61000 581,000.58
Sheet2
 
Thank you. Is there a way to account for different number of varying spaces between the decimal point and the number before the decimal point?
For example, there could be 1, 2, 3 or even 4 spaces.
Book1
AB
1TextWhat I like
2456 23456.23
3789 57789.57
410000 23#VALUE!
5812812.00
61000 581,000.58
Sheet3
Cell Formulas
RangeFormula
B2:B6B2=--SUBSTITUTE(A2," ",".")
 
Upvote 0
Edit: Ah, we posted almost at the same time so my sentence below is now redundant. :biggrin:
It looks like some of your text values have multiple internal spaces so you may need this instead.

25 02 08.xlsm
AB
1TextWhat I like
2456 23456.23
3789 57789.57
410000 2310,000.23
5812812.00
61000 581,000.58
Text to No.s
Cell Formulas
RangeFormula
B2:B6B2=--SUBSTITUTE(TRIM(A2)," ",".")
 
Last edited:
Upvote 0
Absolutely fantastic. Thanks so much. This will save me so much time putting data together into usable form. (y) :)
 
Upvote 0
Is there a way to account for different number of varying spaces between the decimal point and the number before the decimal point?

You can use any of the following options:
DANTE AMOR
AB
1TextWhat I like
2456 23456.23
3789 57789.57
410000 2310000.23
5812812.00
61000 581000.58
Hoja7
Cell Formulas
RangeFormula
B2:B6B2=--SUBSTITUTE(SUBSTITUTE(A2," ",".",1)," ","")


DANTE AMOR
AB
1TextWhat I like
2456 23456.23
3789 57789.57
410000 2310000.23
5812812.00
61000 581000.58
Hoja7
Cell Formulas
RangeFormula
B2:B6B2=SUBSTITUTE(TRIM(A2)," ",".")+0


DANTE AMOR
AB
1TextWhat I like
2456 23456.23
3789 57789.57
410000 2310000.23
5812812.00
61000 581000.58
Hoja7
Cell Formulas
RangeFormula
B2:B6B2=SUBSTITUTE(TRIM(A2)," ",".")*1


:giggle:
 
Upvote 0
Dante, Great. Thanks so much for those solutions. This will really help me at work. :) (y)
 
Upvote 0
I am looking at my slightly different data again and I have the following numbers formatted as text. The last two digits are the numbers after the decimal point.
Is there a way to change this to numbers?
Thanks once again for everyone's help.

Book1
AB
1What I like
26 110 616,110.61
325 080 4525,080.45
410 000 0010,000.00
5143 743 81143,743.81
Sheet1
 
Upvote 0
For those cases, try the following formula, but if you have other cases, with more spaces, with other scenarios that you are not putting in your example, then you should put them.


varios 18feb2025.xlsm
AB
1What I like
26 110 616,110.61
325 080 4525,080.45
410 000 0010,000.00
5143 743 81143,743.81
Hoja11
Cell Formulas
RangeFormula
B2:B5B2=--SUBSTITUTE(SUBSTITUTE(A2," ","",1)," ",".")
 
Upvote 0
Try this for others scenarios:

Excel Formula:
=LET(a,TRIM(RIGHT(SUBSTITUTE(A2," ",REP(" ",99)),99)),SUBSTITUTE(LEFT(A2,LEN(A2)-LEN(a))," ","")&"."&a)+0

varios 18feb2025.xlsm
AB
1What I like
26 110 616,110.61
325 080 4525,080.45
410 000 0010,000.00
5143 743 81143,743.81
61 221.22
72 890 567 332,890,567.33
815 115.10
Hoja11 (2)
 
Upvote 0
Solution

Forum statistics

Threads
1,226,771
Messages
6,192,926
Members
453,767
Latest member
922aloose

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