Having trouble with Right Left Mid

[288enzo]

I don't understand why Right sometimes works and other times not.

2.xls
	F	G	H	I
2	Martinez, Ernesto			Ernesto Martinez
3	Alvarez, Carlos			Carlos Alvarez
4	Estala, Chris			Chris Estala
5	Garcia, Anthony			thony Garcia
6	Perez, Patrick			rick Perez
7	Schultschik, Christian			Christian Schultschik
8	SMID, RONDA			NDA SMID
9	Vela, Davin			vin Vela
10	Atwood, Bret			Bret Atwood
11	Gallaway, Matthew			Matthew Gallaway
12	Hale, Andrew			rew Hale
Report

Cell Formulas
Range	Formula
I2:I12	I2	=RIGHT(F2,FIND(",",F2)-2) & CHAR(10) & LEFT(F2,FIND(",",F2)-1)

I was reading about InStr, and was able to get it to work using Debug.Print, I just don't know how to use it with a WorkSheet.

Debug.Print Left(Cells(7, 6), InStr(Cells(7, 6), ",") - 1)
Debug.Print Mid(Cells(7, 6), InStr(Cells(7, 6), ",") + 2)

If it makes a difference, I'm trying my hand at writing all formulas in R1C1.

This is my origial code that Fluff helped me with -

Sheets("Report").Range(Cells(2, 9), Cells(LR2, 9)).FormulaR1C1 = "=RIGHT(RC[-3],FIND("","",RC[-3])-2) & Char(10) & LEFT(RC[-3],FIND("","",RC[-3])-1)"

I tried replacing Right with Mid in the above, but as you probably have guessed, that did not work.

 

[offthelip]

Your error is Right counts from the right of string not the left , find gives you the number from the left of the string. try this formula:

=RIGHT(F2,LEN(F2)-FIND(",",F2)) & CHAR(10) & LEFT(F2,FIND(",",F2)-1)

 

[Z51]

Try this:

Sheets("Report").Range(Cells(2, 9), Cells(LR2, 9)).FormulaR1C1 = "=MID(RC[-3],FIND("","",RC[-3])+2,LEN(RC[-3]))&CHAR(10)&LEFT(RC[-3],FIND("","",RC[-3])-1)"