R1C1 in macro needs editing

[ndendrinos]

I have 2 workbooks open Workbook A and B
I copy a cell that contains an account number from Workbook A.
e.g. 5180807578203110 (always sixteen digits)
I go to Workbook B and double click in a cell and the account number is pasted
The account number will also change from:
5180807578203110 to
# 518***7578203110

This is the macro I have put together and the R1C1 part needs to work on any account
number (in other words with the Selection)

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = "# 518***7578203110"

End Sub

 

[philR]

I think this is what you want:

replace

ActiveCell.FormulaR1C1 = "# 518***7578203110"

with

temp=activecell
activecell="# " & left(temp,3) & "***" & mid(temp,7)

 

[ndendrinos]

Hello philR and thanks for your help
Tested your idea and it works great for 11 digits numbers but not for 16
Any further ideas ? (tried playing around with formats to no avail)

 

[philR]

That should work fine provided the cells were formatted to text before the numbers were inserted. But then they must be I would think, as Excel will not store a number to 16 places of accuracy.

Are you definitely doing this to text values?

Perhaps you could post the spreadsheet? Or a bit of it at least?

 

[ndendrinos]

Thank you big time
Regards,
Nick