Extract all text after , in a cell

[Dan_D]

Hi all,

I'm trying to extract all the text in a cell which is on the right side of a comma (,)

Currently I am using this formula,

=RIGHT(C1,FIND(",",C1)-1)

However it sometimes is missing some of the data after the , and sometimes pulling a few characters before the ,

any suggestions on how to correct my formula please?

Thanks in advance.

 

[VoG]

Hi. Try

=TRIM(RIGHT(C1,LEN(C1)-FIND(",",C1)))

 

[Dan_D]

Hi VoG,

many thanks for your help, I have 2 questions for you please.

1- This copies the data but can it "take" the data (so it no longer exists in the original cell?
2. if the cell has more then 1 x , can it simply take the data after the last ,?

Thanks for your kind assistance.

 

[VoG]

Hi.

1. A formula cannot do that. You would need VBA.

2. Try

=TRIM(RIGHT(SUBSTITUTE(C1,",",REPT(" ",255)),255))

 

[Dan_D]

Hi VoG,

Many thanks for all your help. Instead of taking out the data I could adjust the formula to pull from the LEFT of the , as opposed to the right.

 

[VoG]

Try like this

Excel Workbook
	C	D	E
1	abc, defg,hijkl, mnop	mnop	abc, defg,hijkl,
Sheet1

 

[Dan_D]

Hi VoG,

Once again many thanks for your kind assistance.

 

[uhayatpk]

Try like this

Sheet1

*	C	D	E
abc, defg,hijkl, mnop	mnop	abc, defg,hijkl,

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:191px;"><col style="width:88px;"><col style="width:166px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula D1 =TRIM(RIGHT(SUBSTITUTE(C1,",",REPT(" ",255)),255)) E1 =SUBSTITUTE(C1,D1,"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thank you Sir JEE. This is very helpful for me.

 

[uncleplum]

Hi,

I'm trying this on this string:

[TABLE="width: 150"]
<tbody>[TR]
[TD="width: 150"]39685~54790@110297535758561678@FULL@0.00@

Sometimes the string may be:

[TABLE="width: 150"]
<tbody>[TR]
[TD="width: 150"]25364~39685~54790@110297535758561678@FULL@0.00@[/TD]
[/TR]
</tbody>[/TABLE]

Trying to extract everything after the last "~" using:

=TRIM(RIGHT(SUBSTITUTE(C1,"~",REPT(" ",255)),255))

Excel says there's an error in the formula when I commit the cell. I'm using Excel 2010.

Can anyone help? Also thanks in advance![/TD]
[/TR]
</tbody>[/TABLE]

 

[uncleplum]

Hi,

[TABLE="width: 150"]
<tbody>[TR]
[TD="width: 150"]
Excel says there's an error in the formula when I commit the cell. I'm using Excel 2010.

[/TD]
[/TR]
</tbody>[/TABLE]

My error.. my list separator was set to ";". Changed back to "," and all is good. Thank you anyway!