texto to column by formula

[doriannjeshi]

Hi,

trying to trim this to the second delimiter by using a formula

111-11-1
result
111-11

exp2
ddd-dd-1
result
ddd-dd

 

[Marcelo Branco]

When I enter this formula in a macro it says "invalid character" refering to "|"

=LEFT(A1,SEARCH("|",SUBSTITUTE(A1,"-","|",2))-1)

This worked for me

Range("B1").Formula = "=LEFT(A1,SEARCH(""|"",SUBSTITUTE(A1,""-"",""|"",2))-1)"

Observe the double double-quotes in the formula string.

M.

 

[doriannjeshi]

I m trying to place
=LEFT(A1,SEARCH("|",SUBSTITUTE(A1,"-","|",2))-1)
in place of the other formula that's there but can't make it work

Range(Cells(2, ActiveCell.Column), Cells(Cells(Rows.Count, ActiveCell.Column - 1).End(xlUp).Row, ActiveCell.Column)).FormulaR1C1 = "=IFERROR(LEFT(RC[-1],FIND(""-"",RC[-1],FIND(""-"",RC[-1])+1)-1),"""")"

 

[Marcelo Branco]

This worked for me

Range(Cells(2, ActiveCell.Column), Cells(Cells(Rows.Count, ActiveCell.Column - 1).End(xlUp).Row, ActiveCell.Column)). _
    FormulaR1C1 = "=LEFT(RC[-1],SEARCH(""|"",SUBSTITUTE(RC[-1],""-"",""|"",2))-1)"

M.

 

[sheetspread]

The | shouldn't be a problem, but the worksheet functions don't always exist in VBA without a .application() or evaluate(). Find and replace are available in both. Show me the whole line and I'll fix it.

 

[Marcelo Branco]

oops... i missed the IFERROR

Try

Range(Cells(2, ActiveCell.Column), Cells(Cells(Rows.Count, ActiveCell.Column - 1).End(xlUp).Row, ActiveCell.Column)). _
    FormulaR1C1 = "=IFERROR(LEFT(RC[-1],SEARCH(""|"",SUBSTITUTE(RC[-1],""-"",""|"",2))-1),"""")"

M.

 

[doriannjeshi]

oops... i missed the IFERROR Try

 Range(Cells(2, ActiveCell.Column), Cells(Cells(Rows.Count, ActiveCell.Column - 1).End(xlUp).Row, ActiveCell.Column)). _     FormulaR1C1 = "=IFERROR(LEFT(RC[-1],SEARCH(""|"",SUBSTITUTE(RC[-1],""-"",""|"",2))-1),"""")"

M.

I get a runtime error 1004

 

[doriannjeshi]

The | shouldn't be a problem, but the worksheet functions don't always exist in VBA without a .application() or evaluate(). Find and replace are available in both. Show me the whole line and I'll fix it.

this is the whole line, it's giving me a runtime error 1004 Sub othertrim() ' ' othertrim Macro ' Range(Cells(2, ActiveCell.Column), Cells(Cells(Rows.Count, ActiveCell.Column - 1).End(xlUp).Row, ActiveCell.Column)). _ FormulaR1C1 = "=IFERROR(LEFT(RC[-1],SEARCH(""|"",SUBSTITUTE(RC[-1],""-"",""|"",2))-1),"""")" ' End Sub

 

[sheetspread]

Marcelo's solution in #15 worked when I tried it:

Excel 2010
	A
1
2	23223-43232365-222-1
3	2334-2211-2-2-23456-86765-2235
4	1-1-1-234565
5	1-9-3456-76543-1111-1257788-54332221
Sheet3

click on B1 (remember, you have an activecell property in the code), then run:

Sub text2columnformulaincells()
Range(Cells(2, ActiveCell.Column), Cells(Cells(Rows.Count, ActiveCell.Column - 1).End(xlUp).Row, ActiveCell.Column)). _
    FormulaR1C1 = "=IFERROR(LEFT(RC[-1],SEARCH(""|"",SUBSTITUTE(RC[-1],""-"",""|"",2))-1),"""")"
End Sub

and you get:

Excel 2010
	A	B
2	23223-43232365-222-1	23223-43232365
3	2334-2211-2-2-23456-86765-2235	2334-2211
4	1-1-1-234565	1-1
5	1-9-3456-76543-1111-1257788-54332221	1-9
Sheet3

Cell Formulas
Range	Formula
B2	=IFERROR(LEFT(A2,SEARCH("|",SUBSTITUTE(A2,"-","|",2))-1),"")
B3	=IFERROR(LEFT(A3,SEARCH("|",SUBSTITUTE(A3,"-","|",2))-1),"")
B4	=IFERROR(LEFT(A4,SEARCH("|",SUBSTITUTE(A4,"-","|",2))-1),"")
B5	=IFERROR(LEFT(A5,SEARCH("|",SUBSTITUTE(A5,"-","|",2))-1),"")

 

[doriannjeshi]

Works like magic. Thank you sheetspread

 

[doriannjeshi]

This worked for me

 Range(Cells(2, ActiveCell.Column), Cells(Cells(Rows.Count, ActiveCell.Column - 1).End(xlUp).Row, ActiveCell.Column)). _     FormulaR1C1 = "=LEFT(RC[-1],SEARCH(""|"",SUBSTITUTE(RC[-1],""-"",""|"",2))-1)"

M.

Thank you Marcelo