Which formula to extract part of the text ?

Inacio11

New Member
Joined
Jul 3, 2018
Messages
33
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>
 

Some videos you may like

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
Joined
Jun 29, 2014
Messages
5,146
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)

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">(VisaPlatin )Santander Informa: Compra APROVADA Cartao VISA final 0292 de R 38,90 em 23/08/18 as 15:12 BBB MODAS INFANTIS</td><td style="text-align: right;;">38,90</td><td style="text-align: right;;">#VALUE !</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">{VisaPlatin}Santander Informa: Compra APROVADA Cartao VISA final 9153 de R$ 162,01 em 23/08/18 as 20:02 POSTO HIGIENOPOLIS</td><td style="text-align: right;;">162,01</td><td style="text-align: right;;">#VALUE !</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Gastos em Moeda: Cantina educandario R$ 20,00</td><td style="text-align: right;;">20,00</td><td style="text-align: right;;">#VALUE !</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">ITAU PERSONNALITE: Cartao final 0512 COMPRA APROVADA 26/08 12:41:06 R$ 9,40 Local: PAD BRASI.</td><td style="text-align: right;;">9,40</td><td style="text-align: right;;">#VALUE !</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet210</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">=TRIM(<font color="Blue">MID(<font color="Red">SUBSTITUTE(<font color="Green">MID(<font color="Purple">A1,FIND(<font color="Teal">" R$ ",SUBSTITUTE(<font color="#FF00FF">A1," R "," R$ "</font>)</font>)+1,255</font>)," ",REPT(<font color="Purple">" ",15</font>)</font>),15,15</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C1</th><td style="text-align:left">=MID(<font color="Blue">SUBSTITUTE(<font color="Red">MID(<font color="Green">A1,FIND(<font color="Purple">" R$ ",SUBSTITUTE(<font color="Teal">A1," R "," R$ "</font>)</font>)+1,255</font>)," ",REPT(<font color="Green">" ",15</font>)</font>),15,15</font>)+0</td></tr></tbody></table></td></tr></table><br />
 

Inacio11

New Member
Joined
Jul 3, 2018
Messages
33
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
Joined
Jun 29, 2014
Messages
5,146
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:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">(VisaPlatin )Santander Informa: Compra APROVADA Cartao VISA final 0292 de R 38,90 em 23/08/18 as 15:12 BBB MODAS INFANTIS</td><td style="text-align: right;;">38,90</td><td style="text-align: right;;">#VALUE!</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">{VisaPlatin}Santander Informa: Compra APROVADA Cartao VISA final 9153 de R$ 162,01 em 23/08/18 as 20:02 POSTO HIGIENOPOLIS</td><td style="text-align: right;;">162,01</td><td style="text-align: right;;">#VALUE!</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Gastos em Moeda: Cantina educandario R$ 20,00</td><td style="text-align: right;;">20,00</td><td style="text-align: right;;">#VALUE!</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">ITAU PERSONNALITE: Cartao final 0512 COMPRA APROVADA 26/08 12:41:06 R$ 9,40 Local: PAD BRASI.</td><td style="text-align: right;;">9,40</td><td style="text-align: right;;">#VALUE!</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Itau Personnalite: realizada transferencia entre contas no valor de R$30,00 em 12/08 as 11:38. Conta debitada: XXX87-7.</td><td style="text-align: right;;">30,00</td><td style="text-align: right;;">#VALUE!</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Itau Personnalite: realizada transferencia entre contas no valor de R30,00 em 12/08 as 11:38. Conta debitada: XXX87-7.</td><td style="text-align: right;;">30,00</td><td style="text-align: right;;">#VALUE!</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet210</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">=TRIM(<font color="Blue">RIGHT(<font color="Red">SUBSTITUTE(<font color="Green">SUBSTITUTE(<font color="Purple">LEFT(<font color="Teal">A1,FIND(<font color="#FF00FF">",",A1</font>)+2</font>),"R$",REPT(<font color="Teal">" ",15</font>)</font>),"R",REPT(<font color="Purple">" ",15</font>)</font>),15</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C1</th><td style="text-align:left">=RIGHT(<font color="Blue">SUBSTITUTE(<font color="Red">SUBSTITUTE(<font color="Green">LEFT(<font color="Purple">A1,FIND(<font color="Teal">",",A1</font>)+2</font>),"R$",REPT(<font color="Purple">" ",15</font>)</font>),"R",REPT(<font color="Green">" ",15</font>)</font>),15</font>)+0</td></tr></tbody></table></td></tr></table><br />

Same description as Post #2 .
 

Inacio11

New Member
Joined
Jul 3, 2018
Messages
33

ADVERTISEMENT

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,871
Office Version
  1. 365
Platform
  1. Windows
Assuming that your string ..
- 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
Joined
Jul 3, 2018
Messages
33

ADVERTISEMENT

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

Watch MrExcel Video

Forum statistics

Threads
1,114,073
Messages
5,545,832
Members
410,709
Latest member
Mrsamir
Top