Help on this function

[adaws]

=TRIM(LEFT(A1,FIND("^",SUBSTITUTE(TRIM(A1)&" "," ","^",5))-1))

what does "^" do in this function?

this function on the whole gives the first five words in a string i just wanted to know what does the above mentioned thing do

[NickyC]

the "substitute" part of the equation finds the fifth space in your text string and replaces it with the caret sign ^
the "find" part of the equation identifies how far this character is from the start of the substituted text string
the "left" part returns the part of the string to the left of this character - which will be the first five words, assuming they are separated by spaces

the caret sign is used because it is unlikely to occur in your text string
any other character could be used for the replacement, but if this character already occurs in the string, it will return an incorrect result

e.g. if the character string is:

one^ two three four five six seven

the formula will return only "one"

If the caret sign is likely to occur in your text string, I suggest you use another character, or a sequence of characters such as &*)*!@#$~^ that will not be in your data

[arlu1201]

Adaws,

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.