# Which formula to extract part of the text ?

#### Inacio11

##### New Member
Hi there
How to extract through formula just the numbers highlighted in red color mark ? Take into consideration sometimes the currency appears as "R\$" and others just "R ". Thanks in advance !

 (VisaPlatin )Santander Informa: Compra APROVADA Cartao VISA final 0292 de R 38,90 em 23/08/18 as 15:12 BBB MODAS INFANTIS {VisaPlatin}Santander Informa: Compra APROVADA Cartao VISA final 9153 de R\$ 162,01 em 23/08/18 as 20:02 POSTO HIGIENOPOLIS Gastos em Moeda: Cantina educandario R\$ 20,00 ITAU PERSONNALITE: Cartao final 0512 COMPRA APROVADA 26/08 12:41:06 R\$ 9,40 Local: PAD BRASI.

<tbody>
</tbody>

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### jtakw

##### Well-known Member
Hi,

Use B1 formula to return results as Text.
Use C1 formula to convert results to Real Numbers. (my sample shows #VALUE ! errors because my regional settings uses the Decimal rather than Comma)

#### Inacio11

##### New Member
Hi jtakw
Thanks for early repply.
It works !!!

As I need the values as number I chose the solution "C1".
By the way I found a new situation that it is not recognized by the proposed formula:
Itau Personnalite: realizada transferencia entre contas no valor de R\$30,00 em 12/08 as 11:38. Conta debitada: XXX87-7.
See on this case the currency "R\$" is adjacent of the number.
Could you please update the formula to also cover this new one situation ?
Thanks !

#### jtakw

##### Well-known Member
Ok, it seems we can't reliably use the " R " & " R\$ " as the delimiter, since there's a chance of " R\$", there may Also be a chance of " R" where the numbers and directly behind without Any Space.
So I'm using a different approach, hopefully, your strings only contain 1 currency value:

Same description as Post #2 .

#### Inacio11

##### New Member

Hi jtakw,
Perfect !
Thanks very much for your support.
Regards,
Inacio

#### Peter_SSs

##### MrExcel MVP, Moderator
- only contains one currency value
- that currency value is always expressed as a decimal (like all you samples)
- if the currency can be 1000 or greater there is no thousands separator (can adjust that if required)
- contains no other commas (like all your samples)
(I think jtakw also assumed all these things?)

.. then I think this should also work for you (up to 999999.99 & if bigger numbers required, add a few more into {4,5,6,7,8,9,....})
Code:
``=AGGREGATE(14,6,RIGHT(LEFT(A1,FIND(",",A1)+2),{4,5,6,7,8,9})+0,1)``

Last edited:

#### Inacio11

##### New Member

Hi Peter !
It also works perfect
Thanks for all.
Regards,
Inacio

You're welcome.

1,147,992
Messages
5,744,212
Members
423,853
Latest member
cathevs09

### 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.

### Which adblocker are you using?

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

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