Text functions

[minsoup]

I have to return the position of the ",", the first name only, and last name only in another column using text functions. I have no idea where to even begin with this. I can of course get one name from one column to another, but if I copy it down the formula doesn't work.

[TMS]

Use FIND to get the position.

Please Login or Register  to view this content.

You can then use that in LEFT, RIGHT and MID functions to determine how many characters to return.


Regards, TMS

[martindwilson]

use find() to get the position of the ,
then use left() and mid() using the value found in find
i'd do it all but i suspect this is some sort of homework

[Loganeb]

In cell B2, put

Please Login or Register  to view this content.

In cell C2, put

Please Login or Register  to view this content.

In cell D2, put

Please Login or Register  to view this content.

[martindwilson]

Nothing is more frustrating than to be working on homework and to get stuck. Without guidance, you might have no hope of finishing the assignment before the deadline. The Internet can be a big help in that respect, but without learning how to get the right answer, asking others to solve your homework questions for you simply becomes a downward spiral (and it's cheating).

We do not want to contribute to you cheating yourself out of your education, but we also acknowledge that seeking assistance to learn a concept is a legitimate request.

If you are genuinely interested in receiving help in the form of tutoring or coaching, then please rephrase the title of your original post to clearly indicate you are seeking coaching or tutoring help. Any forum members (who are willing to assist as a tutor) will modify their responses accordingly to facilitate your learning. Tutors don't tell you the answers, they help you figure it out for yourself; so don't expect answers, expect suggestions, or just plain hints. Also, be specific in describing the function/formula or technique you trying to learn, and tell us what you have attempted so far. Otherwise, expect your plea for homework answers to be ignored.

[AlKey]

Or simply use these two formulas

for First Name:

=TRIM(RIGHT(SUBSTITUTE(A2,",",REPT(" ",25)),25))

for Last Name:

=TRIM(LEFT(SUBSTITUTE(A2,",",REPT(" ",25)),25))

Row\Col	A	B	C
1	Name	First Name - Only	Last Name - Only
2	ackerman, jerry	jerry	ackerman
3	adams, cecilia	cecilia	adams
4	arston, jean	jean	arston
5	aziz, nashir	nashir	aziz
6	bertin, julia	julia	bertin
7	booth, wesley	wesley	booth
8	bose, denise	denise	bose

[MarvinP]

Hi monsoup,

See if the attached isn't what you really want. This trick is to replace the comma with a bunch of spaces. The number of spaces are equal to the length of the original string. Then take the left side and TRIM the spaces away to get the last name and the right side to get the first name. See the attached.