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

 

[pto160]

Thank you so much. Absolutely brilliant. Very dynamic formula.

 

[Peter_SSs]

The last two digits are the numbers after the decimal point.

In that case you could do it with a single substitution like this. (It appears that your 'numbers' have spaces in place of the decimal point and thousand separator so this would also work if you had any larger numbers like row 6)

BTW, I am not sure what post you had marked as the answer before, if any, but can you please mark the post that (for you) best answers the original question, not this new one. The reason is that the marked answer appears immediately below the original question so that future readers can find the question and answer together at the start of the post.

25 02 20.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 143 743 81	1,143,743.81
Text to Number

Cell Formulas
Range	Formula
B2:B6	B2	=SUBSTITUTE(A2," ","")/100

 

[Peter_SSs]

If you did have examples like in post #10, then you could also use this shorter method.

25 02 20.xlsm
	A	B
1	Text	Number
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
Text to Number (2)

Cell Formulas
Range	Formula
B2:B8	B2	=SUBSTITUTE(A2," ","")/10^LEN(TEXTAFTER(A2," ",-1))

 

[pto160]

Peter_SSs, thank you so much for your solution. It works great.
Thank you for explaining about marking the best solution. I did not know that.

 

[Peter_SSs]

Peter_SSs, thank you so much for your solution. It works great.

Good news. Thanks for the confirmation.

Thank you for explaining about marking the best solution. I did not know that.

No problem, but I'm not sure that you have understood as you had left post #10 as the marked solution when it is not a solution to the original question as seen below** so I have unmarked that post. You can mark another one as the solution to the original question if you want.

** In the mini sheet below I have modified the post #10 formula as REP() is not a function in English Excel at least & I'm sure Dante meant the function REPT() instead.

25 02 20.xlsm
	A	B	C
1	Text post 1	What I like	What you get from post 10
2	456 23	456.23	456.23
3	789 57	789.57	789.57
4	10000 23	10000.23	10000.23
5	812	812	0.812
6	1000 58	1000.58	1000.58
Text to Number (3)

Cell Formulas
Range	Formula
C2:C6	C2	=LET(a,TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99)),SUBSTITUTE(LEFT(A2,LEN(A2)-LEN(a))," ","")&"."&a)+0