Adding to part of a number

  • Thread starter Thread starter Connie
  • Start date Start date
C

Connie

Is there a formula that would add 1 to the first part of
this number: 4/800? I would like a formula that would
give 5/800. This, by the way, is not 4 divided by 800.
It merely means 4 weeks out of plant 800. I have a ton of
them to update one week.

Thanks
Connie
 
Hi
try
=(--LEFT(A1,FIND("/",A1)-1)+1) & MID(A1,FIND("/",A1),10)
if A1 stores this number
 
Hi Connie

If you mean by "update" what I fear you mean, then this should be macro work, not people
work. (But I may misunderstand, stranger things happen every day :-)

From here, work in a copy of the file until you're sure it's what you want. This code
changes cell content.

Open the VB editor (Alt F11 or similar). Insert a Module with menus named that. Paste this
in:

Sub test()
Dim L As Long
Dim Parts() As String
Dim Cel As Range
For Each Cel In Selection
If Cel.HasFormula = False Then
If InStr(Cel.Value, "/") > 0 Then
Parts = Split(Cel.Value, "/")
L = Val(Parts(0)) + 1
Cel.Value = "'" & L & "/" & Parts(1)
End If
End If
Next
End Sub

Now return to Excel. Select one or more cells containing your 5/800 things and run "test"
using the Tools > Macro > Run menu. One will be added to each number before / unless
there's a formula or unless there's no / present in the cell.
 
Frank, that works wonderfully! Thank you! Now, that's a
formula I would've never been able to come up with. Nice
to be an Excel guru!! Thanks again! Connie
 
Back
Top