Replace

[Jpernice]

Hello


I need help will a find a replace,

<TABLE style="WIDTH: 352pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=468 border=0><COLGROUP><COL style="WIDTH: 176pt; mso-width-source: userset; mso-width-alt: 8557" span=2 width=234><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 176pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=234 height=17>Invoice Number
A1 B1
</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 176pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=234></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>1001797734-81769</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">-81769</TD></TR></TBODY></TABLE>

I need remove the back end of A1 that is column b1. I use text to column it not work because some invoices number have two "-"

Thanks for your
JP

 

[T. Valko]

Hello


I need help will a find a replace,

<TABLE style="WIDTH: 352pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=468 border=0><COLGROUP><COL style="WIDTH: 176pt; mso-width-source: userset; mso-width-alt: 8557" span=2 width=234><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 176pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=234 height=17>Invoice Number
A1 B1

</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 176pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=234></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>1001797734-81769</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">-81769</TD></TR></TBODY></TABLE>

I need remove the back end of A1 that is column b1. I use text to column it not work because some invoices number have two "-"

Thanks for your
JP

See if this does what you want:

Book1
	A	B
1	1001797734-81769	81769
2	10023-568-1234	1234
3	12345-67890	67890
4	2389-1-120-123	123
Sheet1

This formula entered in B1 and copied down:

=SUBSTITUTE(RIGHT(SUBSTITUTE(A1,"-",REPT("-",100)),100),"-","")

It will extract all text after the LAST (rightmost) dash (hyphen).

The value returned is a TEXT string.