Hi

[piresmdb]

Hi im working with a big table and the text is not separeted from the numbers as the example below

<TABLE style="WIDTH: 342pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=456 border=0><COLGROUP><COL style="WIDTH: 342pt; mso-width-source: userset; mso-width-alt: 16676" width=456><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 342pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=456 height=20>Liabilities to customers under investment contracts 5,124 5,271</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Insurance contract liabilities 7,112 7,188</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Other liabilities 3,002 3,102</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Current tax liabilities 156 139

I am trying to separet it using the text to columns but it is not working there is any way to do it with a macro?
</TD></TR></TBODY></TABLE>

 

[Ruddles]

Hi and welcome to the forum!

If your text is in A1, use the following formula to extract the first numeric value:-

=VALUE(TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),198),99)))

and this formula to extract the final numeric value:-

=VALUE(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)))

 

[piresmdb]

Thank you