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

 

[doriannjeshi]

Maybe this:

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

Unfortunately is deleteing the leading zeroes when I set it to the first delimiter

second delimiter the way you ve set it is fine

 

[sheetspread]

Try:

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

(reply to Post #30)

 

[sheetspread]

Unfortunately is deleteing the leading zeroes when I set it to the first delimiter

second delimiter the way you ve set it is fine

Does formatting the column as text first help?

 

[doriannjeshi]

Does formatting the column as text first help?

In that case gives error and writes the formula

 

[sheetspread]

Yeah same for me, hang on I'll fix it

 

[sheetspread]

This work?

Sub text2colls()
Dim c As Range
For Each c In Range(Cells(2, ActiveCell.Column), Cells(Cells(Rows.Count, ActiveCell.Column - 1).End(xlUp).Row, ActiveCell.Column))
c.Value = ("=IFERROR(LEFT(RC[-1],SEARCH(""|"",SUBSTITUTE(RC[-1],""-"",""|"",2))-1),RC[-1])")
c.Value = "'" & c.Value
Next
End Sub

 

[doriannjeshi]

Sheetspread , it works flawlessly !

Thanks again

 

[doriannjeshi]

This work?

 Sub text2colls() Dim c As Range For Each c In Range(Cells(2, ActiveCell.Column), Cells(Cells(Rows.Count, ActiveCell.Column - 1).End(xlUp).Row, ActiveCell.Column)) c.Value = ("=IFERROR(LEFT(RC[-1],SEARCH(""|"",SUBSTITUTE(RC[-1],""-"",""|"",2))-1),RC[-1])") c.Value = "'" & c.Value Next End Sub

I want to use this formula in the above macro ActiveCell.FormulaR1C1 = "=SUMIF(C[-5],RC[-3],C[-6])" Range("G2").Select How can i do it, the macro it's supposed to execute the sumif formula and copy it down until there is data in the adjacent column and then copy and paste the entire column so the formula goes away.. thanks in advance

 

[sheetspread]

Something like this?

Excel 2010
	A	B	C	D	E
1	Name	Number
2	Q	94			Q
3	W	3			W
4	E	39			E
5	R	19			R
6	T	56			T
7	Y	8			Y
8	U	31			U
9	U	58			I
10	I	20			O
11	O	19			P
12	P	63			A
13	A	12			S
14	S	88			D
15	D	75
16	Q	43
17	W	63
18	E	60
19	R	26
20	Q	68
21	W	100
22	Q	84
23	Q	26
24	Q	41
25	U	63
26	U	32
27	Y	33
28	Y	93
29	Y	62
30	A	34
31	A	89
32	A	74
33	Q	66
Sheet1

Sub countifspaste()
Dim lra%, lre%
lra = Cells(Rows.Count, 1).End(xlUp).Row
lre = Cells(Rows.Count, 5).End(xlUp).Row
Range("F2:F" & lre).FormulaR1C1 = "=sumif(R2C1:R" & lra & "C1,RC[-1],R2C2:R" & lra & "C2)"
End Sub

Excel 2010
	A	B	C	D	E	F
1	Name	Number
2	Q	94			Q	422
3	W	3			W	166
4	E	39			E	99
5	R	19			R	45
6	T	56			T	56
7	Y	8			Y	196
8	U	31			U	184
9	U	58			I	20
10	I	20			O	19
11	O	19			P	63
12	P	63			A	209
13	A	12			S	88
14	S	88			D	75
15	D	75
16	Q	43
17	W	63
18	E	60
19	R	26
20	Q	68
21	W	100
22	Q	84
23	Q	26
24	Q	41
25	U	63
26	U	32
27	Y	33
28	Y	93
29	Y	62
30	A	34
31	A	89
32	A	74
33	Q	66
Sheet1

Cell Formulas
Range	Formula
F2	=SUMIF($A$2:$A$33,E2,$B$2:$B$33)

 

[ButtFace]

Change # to whatever in case "#" occurs in your original string
=LEFT(B21,FIND("#",SUBSTITUTE(B21,"-","#",2))-1)