Which formula to extract part of the text ?

[Inacio11]

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 !

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD] (VisaPlatin )Santander Informa: Compra APROVADA Cartao VISA final 0292 de R 38,90 em 23/08/18 as 15:12 BBB MODAS INFANTIS[/TD]
[/TR]
[TR]
[TD]{VisaPlatin}Santander Informa: Compra APROVADA Cartao VISA final 9153 de R$ 162,01 em 23/08/18 as 20:02 POSTO HIGIENOPOLIS[/TD]
[/TR]
[TR]
[TD]Gastos em Moeda: Cantina educandario R$ 20,00[/TD]
[/TR]
[TR]
[TD]ITAU PERSONNALITE: Cartao final 0512 COMPRA APROVADA 26/08 12:41:06 R$ 9,40 Local: PAD BRASI.[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

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)

Book1
	A	B	C
1	(VisaPlatin )Santander Informa: Compra APROVADA Cartao VISA final 0292 de R 38,90 em 23/08/18 as 15:12 BBB MODAS INFANTIS	38,90	[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=VALUE]#VALUE[/URL] !
2	{VisaPlatin}Santander Informa: Compra APROVADA Cartao VISA final 9153 de R$ 162,01 em 23/08/18 as 20:02 POSTO HIGIENOPOLIS	162,01	[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=VALUE]#VALUE[/URL] !
3	Gastos em Moeda: Cantina educandario R$ 20,00	20,00	[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=VALUE]#VALUE[/URL] !
4	ITAU PERSONNALITE: Cartao final 0512 COMPRA APROVADA 26/08 12:41:06 R$ 9,40 Local: PAD BRASI.	9,40	[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=VALUE]#VALUE[/URL] !
Sheet210

Cell Formulas
Range	Formula
B1	=TRIM(MID(SUBSTITUTE(MID(A1,FIND(" R$ ",SUBSTITUTE(A1," R "," R$ "))+1,255)," ",REPT(" ",15)),15,15))
C1	=MID(SUBSTITUTE(MID(A1,FIND(" R$ ",SUBSTITUTE(A1," R "," R$ "))+1,255)," ",REPT(" ",15)),15,15)+0

 

[Inacio11]

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]

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:

Book1
	A	B	C
1	(VisaPlatin )Santander Informa: Compra APROVADA Cartao VISA final 0292 de R 38,90 em 23/08/18 as 15:12 BBB MODAS INFANTIS	38,90	#VALUE!
2	{VisaPlatin}Santander Informa: Compra APROVADA Cartao VISA final 9153 de R$ 162,01 em 23/08/18 as 20:02 POSTO HIGIENOPOLIS	162,01	#VALUE!
3	Gastos em Moeda: Cantina educandario R$ 20,00	20,00	#VALUE!
4	ITAU PERSONNALITE: Cartao final 0512 COMPRA APROVADA 26/08 12:41:06 R$ 9,40 Local: PAD BRASI.	9,40	#VALUE!
5	Itau Personnalite: realizada transferencia entre contas no valor de R$30,00 em 12/08 as 11:38. Conta debitada: XXX87-7.	30,00	#VALUE!
6	Itau Personnalite: realizada transferencia entre contas no valor de R30,00 em 12/08 as 11:38. Conta debitada: XXX87-7.	30,00	#VALUE!
Sheet210

Cell Formulas
Range	Formula
B1	=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(LEFT(A1,FIND(",",A1)+2),"R$",REPT(" ",15)),"R",REPT(" ",15)),15))
C1	=RIGHT(SUBSTITUTE(SUBSTITUTE(LEFT(A1,FIND(",",A1)+2),"R$",REPT(" ",15)),"R",REPT(" ",15)),15)+0

Same description as Post #2 .

 

[Inacio11]

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

 

[Peter_SSs]

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,....})

=AGGREGATE(14,6,RIGHT(LEFT(A1,FIND(",",A1)+2),{4,5,6,7,8,9})+0,1)

 

[Inacio11]

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

 

[jtakw]

You're welcome.

 

[Peter_SSs]

You're welcome.

Same from me.