Some excel forumla help please

Hi,

Could do with a little help as i thought i could just use the left or right formula for the below but them realised you have to specify how many characters to count so wouldn't work in this case.

If in column A i had a list of names i.e Mr Jones, Miss Smith and so on then in column B i wanted it to show just Mr, Mrs or Miss and in column C i just wanted it to show the name, how would i go about this?

Thanks for any help given.

One method.

In D1 put FIND(" ", A1, 1)
in B1 put LEFT(A1, D1-1)
in C1 put MID(A1, D1+1, LEN(A1)-D1)

Edited by deleted (Tue 18-Oct-11 18:24:38)

Thanks for that but while looking around i have come across a easy solution.

I didn't realise that there was a function under the data tab that converts text to columns which has just proved really usefull.

Thanks anyway.

In reply to a post by Anonymous:

Thanks for that but while looking around i have come across a easy solution.

I didn't realise that there was a function under the data tab that converts text to columns which has just proved really usefull.

Thanks anyway.

Thank you for that, I can see it saving me hours for some jobs and I had never spotted it before. The text manipulation functions in Excel though are great.

Good that you have found that route.

Additionally as the source file is text, I occasionally import them in to WORD or other text editors, do some tidying with their better FIND and REPLACE facilities, then export as text, to import in to EXCEL.

Thank you again, used it twice in the last couple of days.