Maybe MID would fit the bill ?

Perhaps an example to illustrate

Assuming A1 contains:   text1 text2
with similar in A2 down
and we want to split col A into cols B and C

In B1: =LEFT(A1,SEARCH(" ",A1)-1)
In C1: =MID(A1,SEARCH(" ",A1)+1,99)

B1 returns: text1
C1 returns: text2
(the "99" in MID is arbitrary, adjust the number to suit)

To split it similarly for A2, A3, A4 ....,
just select B1:C1 and copy down

Re: I want to get remaining text after using LEFT function

Not a whole lot of detail to go on.

A1 = some string
B1 = some formula using LEFT that returns a portion of the string in A1
C1 = formula to return the rest of the string:

=SUBSTITUTE(A1,B1,"")

Biff