Convert Numbers Text into Numbers

[pto160]

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
	A	B
1	Text	What I like
2	456 23	456.23
3	789 57	789.57
4	10000 23	10,000.23
5	812	812.00
6	1000 58	1,000.58
Sheet2

 

[DanteAmor]

Try

varios 07feb2025.xlsm
	A	B
1	Text	What I like
2	456 23	456.23
3	789 57	789.57
4	10000 23	10000.23
5	812	812.00
6	1000 58	1000.58
Hoja7

Cell Formulas
Range	Formula
B2:B6	B2	=--SUBSTITUTE(A2," ",".")

 

[pto160]

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
	A	B
1	Text	What I like
2	456 23	456.23
3	789 57	789.57
4	10000 23	#VALUE!
5	812	812.00
6	1000 58	1,000.58
Sheet3

Cell Formulas
Range	Formula
B2:B6	B2	=--SUBSTITUTE(A2," ",".")

 

[Peter_SSs]

Edit: Ah, we posted almost at the same time so my sentence below is now redundant.
It looks like some of your text values have multiple internal spaces so you may need this instead.

25 02 08.xlsm
	A	B
1	Text	What I like
2	456 23	456.23
3	789 57	789.57
4	10000 23	10,000.23
5	812	812.00
6	1000 58	1,000.58
Text to No.s

Cell Formulas
Range	Formula
B2:B6	B2	=--SUBSTITUTE(TRIM(A2)," ",".")

 

[pto160]

Absolutely fantastic. Thanks so much. This will save me so much time putting data together into usable form.

 

[DanteAmor]

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
	A	B
1	Text	What I like
2	456 23	456.23
3	789 57	789.57
4	10000 23	10000.23
5	812	812.00
6	1000 58	1000.58
Hoja7

Cell Formulas
Range	Formula
B2:B6	B2	=--SUBSTITUTE(SUBSTITUTE(A2," ",".",1)," ","")

DANTE AMOR
	A	B
1	Text	What I like
2	456 23	456.23
3	789 57	789.57
4	10000 23	10000.23
5	812	812.00
6	1000 58	1000.58
Hoja7

Cell Formulas
Range	Formula
B2:B6	B2	=SUBSTITUTE(TRIM(A2)," ",".")+0

DANTE AMOR
	A	B
1	Text	What I like
2	456 23	456.23
3	789 57	789.57
4	10000 23	10000.23
5	812	812.00
6	1000 58	1000.58
Hoja7

Cell Formulas
Range	Formula
B2:B6	B2	=SUBSTITUTE(TRIM(A2)," ",".")*1

 

[pto160]

Dante, Great. Thanks so much for those solutions. This will really help me at work.

 

[pto160]

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
	A	B
1		What I like
2	6 110 61	6,110.61
3	25 080 45	25,080.45
4	10 000 00	10,000.00
5	143 743 81	143,743.81
Sheet1

 

[DanteAmor]

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
	A	B
1		What I like
2	6 110 61	6,110.61
3	25 080 45	25,080.45
4	10 000 00	10,000.00
5	143 743 81	143,743.81
Hoja11

Cell Formulas
Range	Formula
B2:B5	B2	=--SUBSTITUTE(SUBSTITUTE(A2," ","",1)," ",".")

 

[DanteAmor]

Try this for others scenarios:

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

varios 18feb2025.xlsm
	A	B
1		What I like
2	6 110 61	6,110.61
3	25 080 45	25,080.45
4	10 000 00	10,000.00
5	143 743 81	143,743.81
6	1 22	1.22
7	2 890 567 33	2,890,567.33
8	15 1	15.10
Hoja11 (2)