Technical Discussion
  >> Windows Issues


Register (or login) on our website and you will not see this ad.


  Print Thread
Anonymous
(Unregistered)Tue 18-Oct-11 17:35:15
Print Post

Some excel forumla help please


[link to this post]
 
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.
Standard User john2007
(legend) Tue 18-Oct-11 17:57:09
Print Post

Re: Some excel forumla help please


[re: Anonymous] [link to this post]
 
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 john2007 (Tue 18-Oct-11 18:24:38)

Anonymous
(Unregistered)Tue 18-Oct-11 18:14:10
Print Post

Re: Some excel forumla help please


[re: john2007] [link to this post]
 
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.


Register (or login) on our website and you will not see this ad.

Standard User philippercival
(committed) Tue 18-Oct-11 23:18:22
Print Post

Re: Some excel forumla help please


[re: Anonymous] [link to this post]
 
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.
Anonymous
(Unregistered)Wed 19-Oct-11 06:31:31
Print Post

Re: Some excel forumla help please


[re: philippercival] [link to this post]
 
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.
Standard User philippercival
(committed) Wed 26-Oct-11 16:26:07
Print Post

Re: Some excel forumla help please


[re: Anonymous] [link to this post]
 
Thank you again, used it twice in the last couple of days.
  Print Thread

Jump to