VBA text change

[jaden210]

Hey, I'm looking for some VBA help. preferably an add-in. I have a string of numbers in a cell ex. 2900321543253 that I need changed. the first three characters need to be changed to "978" and then the last character needs to be x+1 so in this case 4. I'm having a hard time getting the function to do all this, and any help will be appreciated.

 

[rob.barnes01]

Try this (with the cell selected):

Sub ConvertValues()

ActiveCell.Value = "978" & Mid(ActiveCell, 4, 9) & Right(ActiveCell, 1) + 1

End Sub

 

[AlphaFrog]

Function MyFunc(rng As Range) As String

    MyFunc = "978" & Mid(rng.Value + 1, 4)
    
End Function

If the value ends in say 59, this will make it end in 60. I wasn't sure if you wanted it to end in 60 or 50 when you add 1 to 9

 

[rob.barnes01]

Jaden,

Alpha's post begs an important question: When the value of the last digit changes from a "9" to a "0", should then the value of the previous digit increase by 1, as well? If so, then the code I suggested will not provide an accurate result.

Edit: Just saw Alpha's follow-up which basically asks the same question ... I'd say great minds think alike, but I'm afraid I'm out of my league here.

 

[jaden210]

sorry for not being more clear with that.. if the number ends in 49 it should go back to 40 and not up to 50.

 

[Rick Rothstein]

I have a feeling this can be shortened some, but I have to leave and have no time to investigate it now. However, using AlphaFrog's setup, this will do what you asked for...

Function MyFunc(rng As Range) As String
  MyFunc = "978" & Mid(Left(rng, Len(rng) - 1) & ((Right(rng, 1) + 1) Mod 10), 4)
End Function

 

[Jonmo1]

Try

Public Function Converter(x As Variant)
x = 978 & Mid(x, 4, Len(x))
x = Left(x, Len(x) - 1) + Right(Right(x, 1) + 1, 1)
Converter = x
End Function

then use in a cell like
=CONVERTER(A1)

 

[mikerickson]

=floor(replace(a1,1,3,"978")+0, 10) + mod(a1+1,10)

 

[AlphaFrog]

sorry for not being more clear with that.. if the number ends in 49 it should go back to 40 and not up to 50.

[color=darkblue]Function[/color] MyFunc(rng [color=darkblue]As[/color] Range) As [color=darkblue]String[/color]

    MyFunc = "978" & Mid(rng.Value + IIf(Right(rng.Value, 1) = "9", -9, 1), 4)

[color=darkblue]End[/color] [color=darkblue]Function[/color]

 

[jaden210]

This is perfect! Is there any way I could possibly use an if then or something so that if the data already starts with 978 it will leave it unaffected? (wont add 1 to the last digit)