Inserting Spaces between two text strings

[Supes77]

Hi Everyone

I have a tricky one.

I have some data that I need to format prior to uploading into a financial system that requires the "invoice number" in a certain format.

The final Cell contents must be 25 Chars in length combining the "Account Number" (which is text format) and the Date (text also), but filling the gap between them with the necessary spaces required. So for the first account number below in the example data, it is 6 Chars, date is 7 Chars, I require 12 Spaces inserted between them (25-6-7=12)

Thanks!!!
Supes

Example of Data:


<TABLE style="WIDTH: 111pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=148 border=0 x:str><COLGROUP><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3797" width=89><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2517" width=59><TBODY><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 67pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=left width=89 height=15>Account Number</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 44pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=left width=59>Date</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15 x:num>007920</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=left x:str="'18MAY11">18MAY11</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15 x:num>250490</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=left x:str="'18MAY11">18MAY11</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15 x:num>2210828</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=left x:str="'18MAY11">18MAY11</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15 x:num>2210828</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=left x:str="'18MAY11">18MAY11</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15 x:num>2210828</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=left x:str="'18MAY11">18MAY11</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15 x:num>2210828</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=left x:str="'18MAY11">18MAY11</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15 x:num>2337399</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=left x:str="'18MAY11">18MAY11</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15 x:num>2337399</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=left x:str="'18MAY11">18MAY11</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15 x:num="6487888951">6487888951</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=left x:str="'18MAY11">18MAY11</TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" height=15 x:num>65546846</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=left x:str="'18MAY11">18MAY11</TD></TR></TBODY></TABLE>

 

[Marcelo Branco]

Hi,

Try this formula in C2
=A2&REPT(" ",25-(LEN(A2)+LEN(B2)))&B2
copy down

HTH

M.

 

[Supes77]

Thank you that worked perfecto!

 

[Marcelo Branco]

Thank you that worked perfecto!

You are welcome!

Tks for providing feedback

M.