Text to Value

[Wimpie]

Good day

I am working in a system where i extract information from.
When I do the extraction i get it in the below format wen is paste it into excel (Offce 365)

Value

R 8 160,00

R 10 890,00

R990,00

R690,00

R 3 760,00

R 1 260,00

<colgroup><col width="74" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2706;"> <tbody> </tbody>

I am trying to convert all the amounts to numbers only
Excel does notseem to recognize the format and can not see the spaces between the hundreds and thousands.
If there are sents i would need to see it too.

Any help would be greatly appreciated

Thank you in advance

 

[Wimpie]

I have tried the below
=SUBSTITUTE(B2;" ";"")
&
=TRIM(B2)
Evn tried find and replace, none of these seem to remove the space between the hundreds and housands

 

[gaz_chops]

try

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),"R",""),",","")+0

 

[Fluff]

Something else to try to remove the space.
=SUBSTITUTE(A1,CHAR(160),"")

 

[Wimpie]

a big "THANK YOU"
gaz_chops your formula still gives me #Value !
fluff if i use your formula and multiply by 1 it works 100%, see the below
=SUBSTITUTE(D10;CHAR(160);"")*1
You just saved me hours of work and hassel

 

[Fluff]

You're welcome & thanks for the feedback

 

[gaz_chops]

Strange, it worked for me!

[TABLE="width: 186"]
<colgroup><col width="99" style="width: 74pt;"><col width="87" style="width: 65pt;"></colgroup><tbody>[TR]
[TD="class: xl63, width: 99"]R 8 160,00[/TD]
[TD="width: 87, align: right"]816000[/TD]
[/TR]
[TR]
[TD="class: xl64"]R 10 890,00[/TD]
[TD="align: right"]1089000[/TD]
[/TR]
[TR]
[TD]890,00[/TD]
[TD="align: right"]89000[/TD]
[/TR]
</tbody>[/TABLE]

But as long as you're sorted

 

[Fluff]

The OP has non-breaking spaces rather than normal spaces.

 

[gaz_chops]

The OP has non-breaking spaces rather than normal spaces.

Ah but I copied & paste them as normal spaces, thx.