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>
 

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
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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
6,007
Office Version
  1. 2016
Platform
  1. Windows
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
51,780
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
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
You're welcome.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top