Preface a number with '

[Trebormac]

I have 2 excel files with columns of serial numbers in each file. The numbers are formatted as numbers in one file and as text with a leading ' in the other file. I need a macro to add the ' to the cells formatted as numbers in order to do a Vlookup.

Thanks for your help!

Trebormac

 

[iliace]

Just FYI, you can do this without a macro. Create a helper column, type this formula (assuming the number is in A2 and down):

=TEXT(A2, "0")

Then copy/paste values on top of your existing numbers.

 

[Trebormac]

Thanks

 

[iliace]

You're welcome... does that work for you, or do you actually need a macro?

 

[Trebormac]

That did not work...do you have a macro?

Thanks again

 

[iliace]

Strange ... worked for me.

Let's try the macro way. To start, select the cells where you want to insert ' before the number, then run this macro:

Public Sub InsertApostrophe()
  Dim rng As Excel.Range
  Dim rngSel As Excel.Range
  Dim s As String
  
  Dim calcs As Excel.XlCalculation
  
  calcs = Application.Calculation
  Application.Calculation = xlCalculationManual
  
  On Error GoTo cleanup
  
  If StrComp(LCase(TypeName(Selection)), "range") <> 0 Then
    Err.Raise 12536, "InsertApostrophe()", "No cell range selected. Operation not valid."
  End If
  
  Set rngSel = Selection
  
  For Each rng In rngSel.Cells
    On Error Resume Next
      s = CStr(rng.Value)
    On Error GoTo cleanup
    If StrComp(Left$(s, 1), "'") <> 0 And IsNumeric(s) Then
      s = "'" & s
      rng.Value = s
    End If
  Next rng
  
cleanup:
  Application.Calculation = calcs
  If Err.Number <> 0 Then
    Call MsgBox("An error occured." & vbCrLf & Err.Number & ": " & Err.Description, vbOKOnly + vbExclamation, Err.Source)
  End If
End Sub

Post back with the result.

 

[Trebormac]

Thanks....That works with slight modification

Trebormac