add or subtract from alpha numeric value

F

fayez_MrExcel

Active Member
Joined
Oct 29, 2005
Messages
439
Office Version
  1. 365
Platform
  1. Windows
I have a alpha numeric code where the number is (always) after the 2nd hyphen on the left, but (sometimes) with different format, one has leading zero and others are not, how can i subtract/add 1 while maintaining the format (see expected value)

Book7
ABCD
1Original ValueExpected Value -/+ 1
2QUA-QT-09-I1QUA-QT-08-I1
3QUA-QT-9-I1QUA-QT-8-I1
4
Sheet1
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
You could use a UDF as below:
VBA Code: 
Function AddToPart(str As String, sVal As Long)
    Dim tmp As Variant
    Dim nTmp As String
    
    tmp = Split(str, "-")
    nTmp = Application.Rept(0, Len(tmp(2)) - Len(tmp(2) * 1)) & ((tmp(2) * 1) + sVal)
    AddToPart = Join(Array(tmp(0), tmp(1), nTmp, tmp(3)), "-")
End Function

And use it like:
Book1
BC
1Original Value
2QUA-QT-09-I1QUA-QT-08-I1
3QUA-QT-9-I1QUA-QT-8-I1
4QUA-QT-0222-I2QUA-QT-0223-I2
5QUA-QT-989-I3QUA-QT-990-I3
Sheet1
Cell Formulas
RangeFormula
C2:C3C2=AddToPart(B2,-1)
C4:C5C4=AddToPart(B4,1)
 
Upvote 0
Here's a formula option:
Book1.xlsb
ABCD
1Original ValueExpected Value -/+ 1Amount to add
2QUA-QT-009-I1QUA-QT-004-I1-5
3QUA-QT-009-I1QUA-QT-005-I1-4
4QUA-QT-009-I1QUA-QT-006-I1-3
5QUA-QT-009-I1QUA-QT-007-I1-2
6QUA-QT-009-I1QUA-QT-008-I1-1
7QUA-QT-009-I1QUA-QT-009-I10
8QUA-QT-009-I1QUA-QT-010-I11
9QUA-QT-009-I1QUA-QT-011-I12
10QUA-QT-009-I1QUA-QT-012-I13
11QUA-QT-009-I1QUA-QT-013-I14
12QUA-QT-009-I1QUA-QT-014-I15
13QUA-QT-09-I1QUA-QT-04-I1-5
14QUA-QT-09-I1QUA-QT-05-I1-4
15QUA-QT-09-I1QUA-QT-06-I1-3
16QUA-QT-09-I1QUA-QT-07-I1-2
17QUA-QT-09-I1QUA-QT-08-I1-1
18QUA-QT-09-I1QUA-QT-09-I10
19QUA-QT-09-I1QUA-QT-10-I11
20QUA-QT-09-I1QUA-QT-11-I12
21QUA-QT-09-I1QUA-QT-12-I13
22QUA-QT-09-I1QUA-QT-13-I14
23QUA-QT-09-I1QUA-QT-14-I15
24QUA-QT-9-I1QUA-QT-4-I1-5
25QUA-QT-9-I1QUA-QT-5-I1-4
26QUA-QT-9-I1QUA-QT-6-I1-3
27QUA-QT-9-I1QUA-QT-7-I1-2
28QUA-QT-9-I1QUA-QT-8-I1-1
29QUA-QT-9-I1QUA-QT-9-I10
30QUA-QT-9-I1QUA-QT-10-I11
31QUA-QT-9-I1QUA-QT-11-I12
32QUA-QT-9-I1QUA-QT-12-I13
33QUA-QT-9-I1QUA-QT-13-I14
34QUA-QT-9-I1QUA-QT-14-I15
35QUA-QT-09-I1QUA-QT-08-I1-1
36QUA-QT-9-I1QUA-QT-8-I1-1
37QUA-QT-0222-I2QUA-QT-0223-I21
38QUA-QT-989-I3QUA-QT-990-I31
39QUA-QT-99-I1QUA-QT-100-I11
Sheet4
Cell Formulas
RangeFormula
C2:C39C2=LET( amountToAdd,D2, start,SEARCH("-",B2,SEARCH("-",B2)+1)+1, finish,SEARCH("-",B2,start+1)-1, length,finish-start+1, newResult,MID(B2,start,length)+amountToAdd, numberOfZerosToPrepend,length-LEN(newResult), LEFT(B2,start-1)&IF(numberOfZerosToPrepend>0,REPT("0",numberOfZerosToPrepend),"")&newResult&RIGHT(B2,LEN(B2)-finish) )
 
Last edited:
Upvote 0
Solution
Hi
VBA option
VBA Code: 
Sub ttest()
    Dim a, x
    Dim i&
    a = Range(Cells(2, 2), Cells(2, 2).End(xlDown)).Cells
    For i = 1 To UBound(a)
        x = Split(a(i, 1), "-")
        If Len(x(2) * 1) = Len(x(2)) Then
            x(2) = x(2) - 1
        Else
            x(2) = "0" & x(2) - 1
        End If
        a(i, 1) = Join(x, "-")
    Next
    Cells(2, 3).Resize(UBound(a)) = a
End Sub
 
Upvote 0
mohadin said:
Hi
VBA option
VBA Code: 
Sub ttest()
    Dim a, x
    Dim i&
    a = Range(Cells(2, 2), Cells(2, 2).End(xlDown)).Cells
    For i = 1 To UBound(a)
        x = Split(a(i, 1), "-")
        If Len(x(2) * 1) = Len(x(2)) Then
            x(2) = x(2) - 1
        Else
            x(2) = "0" & x(2) - 1
        End If
        a(i, 1) = Join(x, "-")
    Next
    Cells(2, 3).Resize(UBound(a)) = a
End Sub
Click to expand...
Thank you
 
Upvote 0
cmowla said:
Here's a formula option:
Book1.xlsb
ABCD
1Original ValueExpected Value -/+ 1Amount to add
2QUA-QT-009-I1QUA-QT-004-I1-5
3QUA-QT-009-I1QUA-QT-005-I1-4
4QUA-QT-009-I1QUA-QT-006-I1-3
5QUA-QT-009-I1QUA-QT-007-I1-2
6QUA-QT-009-I1QUA-QT-008-I1-1
7QUA-QT-009-I1QUA-QT-009-I10
8QUA-QT-009-I1QUA-QT-010-I11
9QUA-QT-009-I1QUA-QT-011-I12
10QUA-QT-009-I1QUA-QT-012-I13
11QUA-QT-009-I1QUA-QT-013-I14
12QUA-QT-009-I1QUA-QT-014-I15
13QUA-QT-09-I1QUA-QT-04-I1-5
14QUA-QT-09-I1QUA-QT-05-I1-4
15QUA-QT-09-I1QUA-QT-06-I1-3
16QUA-QT-09-I1QUA-QT-07-I1-2
17QUA-QT-09-I1QUA-QT-08-I1-1
18QUA-QT-09-I1QUA-QT-09-I10
19QUA-QT-09-I1QUA-QT-10-I11
20QUA-QT-09-I1QUA-QT-11-I12
21QUA-QT-09-I1QUA-QT-12-I13
22QUA-QT-09-I1QUA-QT-13-I14
23QUA-QT-09-I1QUA-QT-14-I15
24QUA-QT-9-I1QUA-QT-4-I1-5
25QUA-QT-9-I1QUA-QT-5-I1-4
26QUA-QT-9-I1QUA-QT-6-I1-3
27QUA-QT-9-I1QUA-QT-7-I1-2
28QUA-QT-9-I1QUA-QT-8-I1-1
29QUA-QT-9-I1QUA-QT-9-I10
30QUA-QT-9-I1QUA-QT-10-I11
31QUA-QT-9-I1QUA-QT-11-I12
32QUA-QT-9-I1QUA-QT-12-I13
33QUA-QT-9-I1QUA-QT-13-I14
34QUA-QT-9-I1QUA-QT-14-I15
35QUA-QT-09-I1QUA-QT-08-I1-1
36QUA-QT-9-I1QUA-QT-8-I1-1
37QUA-QT-0222-I2QUA-QT-0223-I21
38QUA-QT-989-I3QUA-QT-990-I31
39QUA-QT-99-I1QUA-QT-100-I11
Sheet4
Cell Formulas
RangeFormula
C2:C39C2=LET( amountToAdd,D2, start,SEARCH("-",B2,SEARCH("-",B2)+1)+1, finish,SEARCH("-",B2,start+1)-1, length,finish-start+1, newResult,MID(B2,start,length)+amountToAdd, numberOfZerosToPrepend,length-LEN(newResult), LEFT(B2,start-1)&IF(numberOfZerosToPrepend>0,REPT("0",numberOfZerosToPrepend),"")&newResult&RIGHT(B2,LEN(B2)-finish) )
Click to expand...
Thank you so much...
 
Upvote 0
cmowla said:
Here's a formula option:
Book1.xlsb
ABCD
1Original ValueExpected Value -/+ 1Amount to add
2QUA-QT-009-I1QUA-QT-004-I1-5
3QUA-QT-009-I1QUA-QT-005-I1-4
4QUA-QT-009-I1QUA-QT-006-I1-3
5QUA-QT-009-I1QUA-QT-007-I1-2
6QUA-QT-009-I1QUA-QT-008-I1-1
7QUA-QT-009-I1QUA-QT-009-I10
8QUA-QT-009-I1QUA-QT-010-I11
9QUA-QT-009-I1QUA-QT-011-I12
10QUA-QT-009-I1QUA-QT-012-I13
11QUA-QT-009-I1QUA-QT-013-I14
12QUA-QT-009-I1QUA-QT-014-I15
13QUA-QT-09-I1QUA-QT-04-I1-5
14QUA-QT-09-I1QUA-QT-05-I1-4
15QUA-QT-09-I1QUA-QT-06-I1-3
16QUA-QT-09-I1QUA-QT-07-I1-2
17QUA-QT-09-I1QUA-QT-08-I1-1
18QUA-QT-09-I1QUA-QT-09-I10
19QUA-QT-09-I1QUA-QT-10-I11
20QUA-QT-09-I1QUA-QT-11-I12
21QUA-QT-09-I1QUA-QT-12-I13
22QUA-QT-09-I1QUA-QT-13-I14
23QUA-QT-09-I1QUA-QT-14-I15
24QUA-QT-9-I1QUA-QT-4-I1-5
25QUA-QT-9-I1QUA-QT-5-I1-4
26QUA-QT-9-I1QUA-QT-6-I1-3
27QUA-QT-9-I1QUA-QT-7-I1-2
28QUA-QT-9-I1QUA-QT-8-I1-1
29QUA-QT-9-I1QUA-QT-9-I10
30QUA-QT-9-I1QUA-QT-10-I11
31QUA-QT-9-I1QUA-QT-11-I12
32QUA-QT-9-I1QUA-QT-12-I13
33QUA-QT-9-I1QUA-QT-13-I14
34QUA-QT-9-I1QUA-QT-14-I15
35QUA-QT-09-I1QUA-QT-08-I1-1
36QUA-QT-9-I1QUA-QT-8-I1-1
37QUA-QT-0222-I2QUA-QT-0223-I21
38QUA-QT-989-I3QUA-QT-990-I31
39QUA-QT-99-I1QUA-QT-100-I11
Sheet4
Cell Formulas
RangeFormula
C2:C39C2=LET( amountToAdd,D2, start,SEARCH("-",B2,SEARCH("-",B2)+1)+1, finish,SEARCH("-",B2,start+1)-1, length,finish-start+1, newResult,MID(B2,start,length)+amountToAdd, numberOfZerosToPrepend,length-LEN(newResult), LEFT(B2,start-1)&IF(numberOfZerosToPrepend>0,REPT("0",numberOfZerosToPrepend),"")&newResult&RIGHT(B2,LEN(B2)-finish) )
Click to expand...
Georgiboy said:
You could use a UDF as below:
VBA Code: 
Function AddToPart(str As String, sVal As Long)
    Dim tmp As Variant
    Dim nTmp As String
   
    tmp = Split(str, "-")
    nTmp = Application.Rept(0, Len(tmp(2)) - Len(tmp(2) * 1)) & ((tmp(2) * 1) + sVal)
    AddToPart = Join(Array(tmp(0), tmp(1), nTmp, tmp(3)), "-")
End Function

And use it like:
Book1
BC
1Original Value
2QUA-QT-09-I1QUA-QT-08-I1
3QUA-QT-9-I1QUA-QT-8-I1
4QUA-QT-0222-I2QUA-QT-0223-I2
5QUA-QT-989-I3QUA-QT-990-I3
Sheet1
Cell Formulas
RangeFormula
C2:C3C2=AddToPart(B2,-1)
C4:C5C4=AddToPart(B4,1)
Click to expand...
Thank you
 
Upvote 0
Georgiboy said:
You could use a UDF as below:
VBA Code: 
Function AddToPart(str As String, sVal As Long)
    Dim tmp As Variant
    Dim nTmp As String
   
    tmp = Split(str, "-")
    nTmp = Application.Rept(0, Len(tmp(2)) - Len(tmp(2) * 1)) & ((tmp(2) * 1) + sVal)
    AddToPart = Join(Array(tmp(0), tmp(1), nTmp, tmp(3)), "-")
End Function

And use it like:
Book1
BC
1Original Value
2QUA-QT-09-I1QUA-QT-08-I1
3QUA-QT-9-I1QUA-QT-8-I1
4QUA-QT-0222-I2QUA-QT-0223-I2
5QUA-QT-989-I3QUA-QT-990-I3
Sheet1
Cell Formulas
RangeFormula
C2:C3C2=AddToPart(B2,-1)
C4:C5C4=AddToPart(B4,1)
Click to expand...
Thank you
 
Upvote 0
Some much shorter formulas that seem to do the same job as the marked solution.

Firstly, IF you have the TEXTSPLIT function in your Excel 365 version.

22 09 27.xlsm
BCD
1Original ValueExpected Value -/+ 1Amount to add
2QUA-QT-009-I1QUA-QT-004-I1-5
3QUA-QT-009-I1QUA-QT-005-I1-4
4QUA-QT-009-I1QUA-QT-006-I1-3
5QUA-QT-009-I1QUA-QT-007-I1-2
6QUA-QT-009-I1QUA-QT-008-I1-1
7QUA-QT-009-I1QUA-QT-009-I10
8QUA-QT-009-I1QUA-QT-010-I11
9QUA-QT-009-I1QUA-QT-011-I12
10QUA-QT-009-I1QUA-QT-012-I13
11QUA-QT-009-I1QUA-QT-013-I14
12QUA-QT-009-I1QUA-QT-014-I15
13QUA-QT-09-I1QUA-QT-04-I1-5
14QUA-QT-09-I1QUA-QT-05-I1-4
15QUA-QT-09-I1QUA-QT-06-I1-3
16QUA-QT-09-I1QUA-QT-07-I1-2
17QUA-QT-09-I1QUA-QT-08-I1-1
18QUA-QT-09-I1QUA-QT-09-I10
19QUA-QT-09-I1QUA-QT-10-I11
20QUA-QT-09-I1QUA-QT-11-I12
21QUA-QT-09-I1QUA-QT-12-I13
22QUA-QT-09-I1QUA-QT-13-I14
23QUA-QT-09-I1QUA-QT-14-I15
24QUA-QT-9-I1QUA-QT-4-I1-5
25QUA-QT-9-I1QUA-QT-5-I1-4
26QUA-QT-9-I1QUA-QT-6-I1-3
27QUA-QT-9-I1QUA-QT-7-I1-2
28QUA-QT-9-I1QUA-QT-8-I1-1
29QUA-QT-9-I1QUA-QT-9-I10
30QUA-QT-9-I1QUA-QT-10-I11
31QUA-QT-9-I1QUA-QT-11-I12
32QUA-QT-9-I1QUA-QT-12-I13
33QUA-QT-9-I1QUA-QT-13-I14
34QUA-QT-9-I1QUA-QT-14-I15
35QUA-QT-09-I1QUA-QT-08-I1-1
36QUA-QT-9-I1QUA-QT-8-I1-1
37QUA-QT-0222-I2QUA-QT-0223-I21
38QUA-QT-989-I3QUA-QT-990-I31
39QUA-QT-99-I1QUA-QT-100-I11
+-
Cell Formulas
RangeFormula
C2:C39C2=LET(term3,INDEX(TEXTSPLIT(B2,"-"),3),SUBSTITUTE(B2,term3,TEXT(term3+D2,REPT(0,LEN(term3)))))


Secondly, if you don't have that function (yet)

22 09 27.xlsm
BCD
1Original ValueExpected Value -/+ 1Amount to add
2QUA-QT-009-I1QUA-QT-004-I1-5
3QUA-QT-009-I1QUA-QT-005-I1-4
4QUA-QT-009-I1QUA-QT-006-I1-3
5QUA-QT-009-I1QUA-QT-007-I1-2
6QUA-QT-009-I1QUA-QT-008-I1-1
7QUA-QT-009-I1QUA-QT-009-I10
8QUA-QT-009-I1QUA-QT-010-I11
9QUA-QT-009-I1QUA-QT-011-I12
10QUA-QT-009-I1QUA-QT-012-I13
11QUA-QT-009-I1QUA-QT-013-I14
12QUA-QT-009-I1QUA-QT-014-I15
13QUA-QT-09-I1QUA-QT-04-I1-5
14QUA-QT-09-I1QUA-QT-05-I1-4
15QUA-QT-09-I1QUA-QT-06-I1-3
16QUA-QT-09-I1QUA-QT-07-I1-2
17QUA-QT-09-I1QUA-QT-08-I1-1
18QUA-QT-09-I1QUA-QT-09-I10
19QUA-QT-09-I1QUA-QT-10-I11
20QUA-QT-09-I1QUA-QT-11-I12
21QUA-QT-09-I1QUA-QT-12-I13
22QUA-QT-09-I1QUA-QT-13-I14
23QUA-QT-09-I1QUA-QT-14-I15
24QUA-QT-9-I1QUA-QT-4-I1-5
25QUA-QT-9-I1QUA-QT-5-I1-4
26QUA-QT-9-I1QUA-QT-6-I1-3
27QUA-QT-9-I1QUA-QT-7-I1-2
28QUA-QT-9-I1QUA-QT-8-I1-1
29QUA-QT-9-I1QUA-QT-9-I10
30QUA-QT-9-I1QUA-QT-10-I11
31QUA-QT-9-I1QUA-QT-11-I12
32QUA-QT-9-I1QUA-QT-12-I13
33QUA-QT-9-I1QUA-QT-13-I14
34QUA-QT-9-I1QUA-QT-14-I15
35QUA-QT-09-I1QUA-QT-08-I1-1
36QUA-QT-9-I1QUA-QT-8-I1-1
37QUA-QT-0222-I2QUA-QT-0223-I21
38QUA-QT-989-I3QUA-QT-990-I31
39QUA-QT-99-I1QUA-QT-100-I11
+- (2)
Cell Formulas
RangeFormula
C2:C39C2=LET(term3,TRIM(MID(SUBSTITUTE(B2,"-",REPT(" ",20)),40,20)),SUBSTITUTE(B2,term3,TEXT(term3+D2,REPT(0,LEN(term3)))))
 
Upvote 0
Peter_SSs said:
Some much shorter formulas that seem to do the same job as the marked solution.
Click to expand...
Not as much as it appears. (Your shortest solution is a little more than half as long. Your longer solution, 30% shorter. But I thought to leave in meaningful variable names so that it makes sense. But to each his own.)

Book1.xlsb
ABCD
1Original ValueExpected Value -/+ 1Amount to add
2QUA-QT-009-I1QUA-QT-004-I1-5
3QUA-QT-009-I1QUA-QT-005-I1-4
4QUA-QT-009-I1QUA-QT-006-I1-3
5QUA-QT-009-I1QUA-QT-007-I1-2
6QUA-QT-009-I1QUA-QT-008-I1-1
7QUA-QT-009-I1QUA-QT-009-I10
8QUA-QT-009-I1QUA-QT-010-I11
9QUA-QT-009-I1QUA-QT-011-I12
10QUA-QT-009-I1QUA-QT-012-I13
11QUA-QT-009-I1QUA-QT-013-I14
12QUA-QT-009-I1QUA-QT-014-I15
13QUA-QT-09-I1QUA-QT-04-I1-5
14QUA-QT-09-I1QUA-QT-05-I1-4
15QUA-QT-09-I1QUA-QT-06-I1-3
16QUA-QT-09-I1QUA-QT-07-I1-2
17QUA-QT-09-I1QUA-QT-08-I1-1
18QUA-QT-09-I1QUA-QT-09-I10
19QUA-QT-09-I1QUA-QT-10-I11
20QUA-QT-09-I1QUA-QT-11-I12
21QUA-QT-09-I1QUA-QT-12-I13
22QUA-QT-09-I1QUA-QT-13-I14
23QUA-QT-09-I1QUA-QT-14-I15
24QUA-QT-9-I1QUA-QT-4-I1-5
25QUA-QT-9-I1QUA-QT-5-I1-4
26QUA-QT-9-I1QUA-QT-6-I1-3
27QUA-QT-9-I1QUA-QT-7-I1-2
28QUA-QT-9-I1QUA-QT-8-I1-1
29QUA-QT-9-I1QUA-QT-9-I10
30QUA-QT-9-I1QUA-QT-10-I11
31QUA-QT-9-I1QUA-QT-11-I12
32QUA-QT-9-I1QUA-QT-12-I13
33QUA-QT-9-I1QUA-QT-13-I14
34QUA-QT-9-I1QUA-QT-14-I15
35QUA-QT-09-I1QUA-QT-08-I1-1
36QUA-QT-9-I1QUA-QT-8-I1-1
37QUA-QT-0222-I2QUA-QT-0223-I21
38QUA-QT-989-I3QUA-QT-990-I31
39QUA-QT-99-I1QUA-QT-100-I11
Sheet4
Cell Formulas
RangeFormula
C2:C39C2=LET(s,SEARCH("-",B2,SEARCH("-",B2)+1)+1,f,SEARCH("-",B2,s+1)-1,l,f-s+1,r,MID(B2,s,l)+D2,z,l-LEN(r),LEFT(B2,s-1)&IF(z>0,REPT("0",z),"")&r&RIGHT(B2,LEN(B2)-f))
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Need to add two sets of minutes with custom formatting and round
Replies
1
Views
106
bh24524
B
J
  • Question
Add text from files in a directory to new sheet
Replies
1
Views
345
Jacomatic
J
S
  • Question
VBA to ignore certain MAC Address values but process all others
Replies
1
Views
298
myall_blues
myall_blues
P
  • Question
**Add existing date & time to another cell with condition**
2
Replies
16
Views
377
psc1
P
E
  • Question
Function to return array of 10 largest absolute values while maintaining sign
Replies
0
Views
121
eurobonds
E

Forum statistics

Threads
1,216,101
Messages
6,128,845
Members
449,471
Latest member
lachbee

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top