try using the functions right and left

c1"=right(your_cell,number_of_characters_to_extract)"
c2"=left(your_cell,number_of_characters_to_extract)"

then use "=concatenate(c2,c1)"

If you know the width of the receiving cell, you could use a formula like:

=Sheet1!C1&REPT(" ",40-LEN(Sheet1!C1&Sheet1!C2))&Sheet1!C2

I made the width of the string 40 characters wide.  With a proportional font,
you may have to experiment with what number you need to make it look pretty.

I assume that you mean you have 5 cells merged.  If that is the case then
unmerge the cells and put the data left aligned in the left most cell and
right aligned in the right most cell.  If you want to keep the cells merged
try:

Format > Cells > Number > Custom

Enter the custon format:
"Test it our to see"* #"If this will work for you"

Note that there is a space between the * and the #