# 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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

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