Break CCard Number into sets of four

[stokie21]

Hi guys, How do i get a cell to break a card number into sets of 4?

at the moment its in one big lump!

1234567891234567

i want the satff to enter the card number as above, but i also want excel to spit it for me so it reads 1234 5678 9123 4567


Thanks

 

[stokie21]

sorry i missed an important bit

The card number cell is c29,d29,e29,f29,g29,h29 i have made all those cells 1 big cell

sorry i missed that off

 

[stokie21]

Just done Stnkynts and tc88 formula's and both worked. Only problem is that i need it to edit the cell i am working on, so as soon as i have finished entering the card number it splits in that cell

thanks

 

[stokie21]

Why not just apply a custom numberformat like this :
0000-0000-0000-0000
to all the cells you want this behaviour in?

just tried this one, i did the custom number and it almost worked. If you setup a custom cell and enter 1234123412341234 if splits it just as i wanted, but for some reason the last number changes to an 0, so it now reads 1234-1234-1234-1230

 

[Hermanito]

hmm, you're right, didn't notice that before

Has something to do with maximum 15 significant digits I think...

In that case, you're gonna have to go with Norie's code, change the Cells(1, 1) into Cells(29,3) for your particular case...

 

[stokie21]

Yes i want to give that ago but am not sure how to Enter it!

I have merged cells CDEFGH into one cell, to give more room for the card number its on Row 29

any ideas how to enter it? and where it goes?

Thanks

 

[Hermanito]

the merging doesn't matter, for VBA you just use the leftmost cell as reference.

The code Norie gave should be pasted in the worksheet-module for the sheet where you need it: rightclick on the tab of that worksheet and choose 'View Code'. Paste Norie's code in there...

Then try to enter a creditcard number in the cell and behold the magic when you enter

Edit: I noticed Norie's code is missing a close bracket in the first line... just add that to the end of the line

 

[Norie]

Do you really need the merged cells?

I don't think the code I posted will work with them.

 

[stokie21]

the merging doesn't matter, for VBA you just use the leftmost cell as reference.

The code Norie gave should be pasted in the worksheet-module for the sheet where you need it: rightclick on the tab of that worksheet and choose 'View Code'. Paste Norie's code in there...

Then try to enter a creditcard number in the cell and behold the magic when you enter

Edit: I noticed Norie's code is missing a close bracket in the first line... just add that to the end of the line

I did it but code did not work here is a copy

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> Cells(29, 3).Address Then Exit Sub
If Len(Target.Value) <> 16 Then Exit Sub

Application.EnableEvents = False

Target.Value = Format(Target.Value, "0000 0000 0000 0000")

Application.EnableEvents = True

End Sub


I also took off the merged cell, only problem is that it pushes my other stuff off the page, I really could do with using merged cells for this part. Any ideas where i am going wrong?

cheers

 

[Norie]

It does work with merged cells, but I still wouldn't recommend using them.

Try formatting the cells as Center Across Selection.

As for the code not working, where did you put it?

It should go in the worksheet module of whatever worksheet the credit card details are being entered in.

 

[Hermanito]

I just tested it myself...

it works fine with the merged cells, on one condition: you need to set that cell to Text format. Otherwise, the input is still being parsed as a number, and then the second check of Norie's code fails. Also, that pesky end zero is back.

When you change the cell format to Text, it works fine.