Copy text after last comma in cell

[Juleew]

I am using excel 2007, I have a column of cells that contain text that is separated by commas. Each cell may have 3 commas or 5 or 2 commas. What I need to do is copy the last text after the last comma in the cell.

example
xxx,yyy,zzz copy to another cell the text zzz
xxx,yyy copy to another cell the text yyy
xxx,yyy,zzz,aaa copy to another cell the text aaa

 

[István Hirsch]

If you want to put the result in another cell, give this formula a try:

=REPLACE(A1,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1,"")

 

[ZVI]

One more formula: =SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ",",")

 

[billszysz]

or

=SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ",",")

oops..... sorry ZVI

 

[ZVI]

oops.....

At last I am faster!

 

[babulalgandhi]

Example

Row A
1. apple,boy,cat,day,eat
2. eye,floor,good,help
3. pick,query,rose,sun,toy
4. x,y,z

I Want To Split Last Word or Last Comma Of Each Row Data

Row A | Row B
1. apple,boy,cat,day | eat
2. eye,floor,good | help
3. pick,query,rose,sun | toy
4. x,y | z

 

[shawnw]

I have a similar question.

• I need to extract EACH value within a comma-delimited series .
• I do not know exactly how many items will appear within a series, but I know it will always be 10 or fewer.
• I do not know the number of characters of any of the values.

Example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]COL A[/TD]
[/TR]
[TR]
[TD]Apple, Banana, Pear[/TD]
[/TR]
[TR]
[TD]Banana, Watermelon, Grape[/TD]
[/TR]
[TR]
[TD]Grape, Lemon, Watermelon, Kiwi[/TD]
[/TR]
[TR]
[TD]Lemon, Lime, Kiwi, Pear[/TD]
[/TR]
</tbody>[/TABLE]

I'd like the following to be returned in columns B through whatever.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]COL A[/TD]
[TD]COL B[/TD]
[TD]COL C[/TD]
[TD]COL D[/TD]
[TD]COL E[/TD]
[TD]COL F[/TD]
[/TR]
[TR]
[TD]Apple, Banana, Pear[/TD]
[TD]Apple[/TD]
[TD]Banana[/TD]
[TD]Pear[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Banana, Watermelon, Grape[/TD]
[TD]Banana[/TD]
[TD]Watermelon[/TD]
[TD]Grape[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grape, Lemon, Watermelon, Kiwi[/TD]
[TD]Pear[/TD]
[TD]Lemon[/TD]
[TD]Watermelon[/TD]
[TD]Kiwi[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Lemon, Lime, Kiwi, Pear[/TD]
[TD]Lemon[/TD]
[TD]Lime[/TD]
[TD]Kiwi[/TD]
[TD]Pear[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I figure I'll need to use the FIND(), MID(), and TRIM() functions, but I can't seem to figure it out.

Thanks,

Shawn

 

[Rick Rothstein]

Assuming your data in Column A starts on Row 1, put this formula in cell B1 and copy it across for as many columns as you think you might ever need to fill, then copy all of those cells down to the bottom of your data...

=TRIM(MID(SUBSTITUTE(", "&$A1,", ",REPT(" ",300)),COLUMNS($B:B)*300,300))

 

[shawnw]

I have no spaces after the commas, so with a little tweaking that worked perfectly. I now see the data separated out. My next step is figuring out how to turn this into a pie chart. Thanks!

 

[István Hirsch]

... Or you can use Data/Text to Columns with comma as a delimiter.

 

[Rick Rothstein]

I have no spaces after the commas, so with a little tweaking that worked perfectly.

Your example data showed a space after the comma which is why I included the space along with the comma as the delimiter... you should be careful to show us what you actually have when asking questions in a forum (remember, the people you are asking to help you only know what you show or tell us, nothing more, nothing less).