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

 

[VoG]

Try this

Excel Workbook
	A	B	C	D	E
1	1234567891234567	1234	5678	9123	4567
Sheet4

 

[tc88]

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

Do you want the 16 digits separated into 4 cells of 4 or all in one cell with spaces between?

 

[stnkynts]

If you want the 16 digits in one cell try

=LEFT(A1,4) & " " & MID(A1,5,4) & " " & MID(A1,9,4) & " " & RIGHT(A1,4)

 

[stokie21]

Do you want the 16 digits separated into 4 cells of 4 or all in one cell with spaces between?

the user enters it in one cell, at that same cell will spilt it into groups of 4

NOT 4 different cells

Thanks

 

[tc88]

Try this

Excel Workbook
	A	B	C	D	E
1	1234567891234567	1234	5678	9123	4567
Sheet4

I would use these formulas with a CONCATENATE function:

=CONCATENATE(MID(A1,1,4)," ",MID(A1,5,4)," ",MID(A1,9,4)," ",MID(A1,13,4))

 

[stokie21]

If you want the 16 digits in one cell try

=LEFT(A1,4) & " " & MID(A1,5,4) & " " & MID(A1,9,4) & " " & RIGHT(A1,4)

Thaks for this where do i enter it?

If i put it in the FX bar it shows it in the cell


Thanks

 

[tc88]

Thaks for this where do i enter it?

If i put it in the FX bar it shows it in the cell


Thanks

You can't put the function in the cell that the function references. You'll need to put the function in column B or something.

 

[Norie]

You'll need code to split the credit card number in cell.

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range
 
   If Target.Address <> Cells(1, 1).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

 

[Hermanito]

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