Formula Halp - If or Lookup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Diane - 07 May 2008 16:56 GMT

I am not sure how to get the results I am looking for, and if it is better to
use an if statement or a lookup funtcion (which I have never used before).

Data Information:
Column "AO" contains one of 5 names
Based on looking at these names, I want a formula that puts one of 5
corresponding intials in column BA.

Any formula suggestions would be truly appreciated!!

Reply to this Message

John Bundy - 07 May 2008 17:04 GMT

We need some rules, what decides which initials go into column BA? you say
colmn AO contains one of 5 names, so does AO contain only 1 name? or does it
have many names that are from a pool of 5?

Signature

-John
Please rate when your question is answered to help us and others know what
is helpful.

> I am not sure how to get the results I am looking for, and if it is better to
> use an if statement or a lookup funtcion (which I have never used before).
[quoted text clipped - 5 lines]
>
> Any formula suggestions would be truly appreciated!!

Reply to this Message

Diane - 07 May 2008 17:45 GMT

I'll try to illustrate more clearly. Column AO is a long list within a
spreadsheet database of over 300 entries. I want Column BA to display the
appropriate initials. So if AO had Jane Doe, or Jane Doe, LCSW, column BA's
formula would yield the answer JD.

Example:
Column AO Column BA
Jane Doe JD
Sally Smith, LMSW SS
Bob Brown BB
Sue White, LCSW SW
Mary Jane, LMSW MJ
Mary Jane MJ
Bob Brown, LCSW-R BB
Jane Doe, LCSW JD
Sally Smith SS

Is there a formula that can do this? Please let me know if you need any
further info to assist in helping me, and thank you for responding so quickly
to my original post.

> We need some rules, what decides which initials go into column BA? you say
> colmn AO contains one of 5 names, so does AO contain only 1 name? or does it
[quoted text clipped - 9 lines]
> >
> > Any formula suggestions would be truly appreciated!!

Reply to this Message

Fred Smith - 07 May 2008 18:13 GMT

Try this in column BA:

=left(ao1,1)&left(find(" ",ao1)+1,1)

Copy down as required

Regards,
Fred.

> I'll try to illustrate more clearly. Column AO is a long list within a
> spreadsheet database of over 300 entries. I want Column BA to display the
[quoted text clipped - 36 lines]
>> >
>> > Any formula suggestions would be truly appreciated!!

Reply to this Message

dan dungan - 07 May 2008 18:15 GMT

Does the formula below work for you?

=LEFT(AO1,1)&MID(AO1,FIND(" ",AO1)+1,1)

Reply to this Message

Diane - 07 May 2008 18:32 GMT

THANKS! It worked perfectly!!

> Does the formula below work for you?
>
> =LEFT(AO1,1)&MID(AO1,FIND(" ",AO1)+1,1)

Reply to this Message