Add the data

[sksanjeev786]

Hi Team,

Need to add from A2 to E2 output answer I have kept in G2

book2
	A	B	C	D	E	F	G
1							Total from A2 to E2
2	48%F	36%	16%EJ	-	-		100
3	28%	36%	14%GR	5%	6%CJR		89
4	33%	31%	13%E	6%HJ	4%JR		87
5	32%F	33%	18%G	8%	5%JR		96
6	27%DI	29%D	10%	6%	6%R		78
7	38%	32%	15%	5%	4%IJR		94
8	39%F	40%R	14%GJ	5%EJ	1%J		99
9	40%DFI	36%	15%E	4%E	3%EJR		98
10	35%DFI	40%	17%EJ	5%CE	2%J		99
11	20%FI	30%	21%R	12%R	8%CEJR		91
12	13%I	22%CF	21%	13%	13%D		82
13	28%F	23%	12%C	7%J	5%		75
14	19%I	13%I	6%	4%	9%G		51
Sheet1

Regards,
Sanjeev

 

[johnnyL]

One way of doing it is copy/paste the following function code to a module:

Function GetNumeric(CellRef As String)
'
    Dim StringLength As Integer
'
    StringLength = Len(CellRef)
'
    For i = 1 To StringLength
        If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1)
    Next
'
    GetNumeric = Result
End Function

After that you can use Formulas on your sheet, for example to Add up A2 through E2 You would use the following formula:
= SUM(GetNumeric(A2), GetNumeric(B2), GetNumeric(C2), GetNumeric(D2), GetNumeric(E2))

You can then drag that formula down the column.

 

[Peter_SSs]

You could also try with standard worksheet functions

21 12 12.xlsm
	A	B	C	D	E	F	G
1							Total from A2 to E2
2	48%F	36%	16%EJ	-	-		100
3	28%	36%	14%GR	5%	6%CJR		89
4	33%	31%	13%E	6%HJ	4%JR		87
5	32%F	33%	18%G	8%	5%JR		96
6	27%DI	29%D	10%	6%	6%R		78
7	38%	32%	15%	5%	4%IJR		94
8	39%F	40%R	14%GJ	5%EJ	1%J		99
9	40%DFI	36%	15%E	4%E	3%EJR		98
10	35%DFI	40%	17%EJ	5%CE	2%J		99
11	20%FI	30%	21%R	12%R	8%CEJR		91
12	13%I	22%CF	21%	13%	13%D		82
13	28%F	23%	12%C	7%J	5%		75
14	19%I	13%I	6%	4%	9%G		51
Sum

Cell Formulas
Range	Formula
G2:G14	G2	=SUMPRODUCT(IFERROR(--LEFT(A2:E2,FIND("%",A2:E2&"%")),0))*100

 

[sksanjeev786]

One way of doing it is copy/paste the following function code to a module:

Function GetNumeric(CellRef As String)
'
    Dim StringLength As Integer
'
    StringLength = Len(CellRef)
'
    For i = 1 To StringLength
        If IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1)
    Next
'
    GetNumeric = Result
End Function

After that you can use Formulas on your sheet, for example to Add up A2 through E2 You would use the following formula:
= SUM(GetNumeric(A2), GetNumeric(B2), GetNumeric(C2), GetNumeric(D2), GetNumeric(E2))

You can then drag that formula down the column.

Thank you so much Johnn for your hard work!!!!

 

[sksanjeev786]

You could also try with standard worksheet functions

21 12 12.xlsm
	A	B	C	D	E	F	G
1							Total from A2 to E2
2	48%F	36%	16%EJ	-	-		100
3	28%	36%	14%GR	5%	6%CJR		89
4	33%	31%	13%E	6%HJ	4%JR		87
5	32%F	33%	18%G	8%	5%JR		96
6	27%DI	29%D	10%	6%	6%R		78
7	38%	32%	15%	5%	4%IJR		94
8	39%F	40%R	14%GJ	5%EJ	1%J		99
9	40%DFI	36%	15%E	4%E	3%EJR		98
10	35%DFI	40%	17%EJ	5%CE	2%J		99
11	20%FI	30%	21%R	12%R	8%CEJR		91
12	13%I	22%CF	21%	13%	13%D		82
13	28%F	23%	12%C	7%J	5%		75
14	19%I	13%I	6%	4%	9%G		51
Sum

Cell Formulas
Range	Formula
G2:G14	G2	=SUMPRODUCT(IFERROR(--LEFT(A2:E2,FIND("%",A2:E2&"%")),0))*100

Thank you So much Peter

As mentioned earlier you always Rockkkkk!!!

 

[Peter_SSs]

Glad to help.