formulae for adding a forward slash automatically into a cell

  • Thread starter Thread starter angelgrant
  • Start date Start date
A

angelgrant

Hi every1,

I'm sort new to excel and im looking for a formulae to automaticall
add a forward slash after every 3rd number and before the 4th number.

If anyone can help it would be greatly appreciated

Thank
 
angelgrant said:
I'm sort new to excel and im looking for a formulae to automatically
add a forward slash after every 3rd number and before the 4th number.
Click to expand...

One try ..

Assuming numbers in A1 down,

Put in B1:
=IF(LEN(A1)=0,"",IF(LEN(A1)<4,A1,LEFT(A1,3)&"\"&MID(A1,4,99)))
Copy down

I've assumed that numbers less than 4 digits are to be left untouched
(the "99" in MID is just an arbitrary figure)

---
 
This one assumes that no slash should be desired it the value were TEXT.

=IF(ISTEXT(A1),A1,IF(LEN(A1)=0,"",IF(LEN(A1)<4,A1,LEFT(A1,3)&"\"&MID(A1,4,99))))

Vaya con Dios,
Chuck, CABGx3
 
If the characters are all numbers, and the number of characters is
consistent, you could use cell formatting:

Select the cells, and choose Format>Cells
On the Number tab, select the Custom category
In the text box, enter: 000"/"000
Click OK
 
Back
Top