Format Text

  • Thread starter Thread starter Tazflerts
  • Start date Start date
Why a macro...Here is a formula to do it...

=LEFT(A1, 1) &"-" & MID(A1, 2, 3) &"-" & MID(A1, 5, 2) &"-" & MID(A1, 7, 2)

With your text in cell A1
 
Hey Gents,

Thanks for the response.

To answer the question: The data returned is from a msquery from other
tables in the workbook. This occupies from Column A to U. To use the
formula would mean inserting or using a column for the formula but when the
data is refreshed it would mean recalibrating the formulas again.

Instead i have implemented the formula provided in a macro and then create a
button with the macro assigned to it. This way the maro runs on the specific
column and return the changes to the same column.

This is the macro::::::

Public Sub NISFormat()

Dim rCell As Range

For Each rCell In Selection.Cells



With rCell



.Value = Left(rCell.Value, 1) & "-" & Mid(rCell.Value, 2, 2)
& "-" & Right(rCell.Value, 4)



End With



Next rCell

End Sub

:::::::::::::::::::::::::::::::::

Thanks again.
 
How can i format text by macro for K1234567 to K-123-45-67


Thanks,
Kevin
Click to expand...


Perhaps:

==============================
Option Explicit
Sub FormatSpecial()
Dim c As Range
For Each c In Selection
If InStr(c.Value, "-") = 0 And _
Len(c.Value) = 8 Then
c.Value = Format(c.Value, "@-@@@-@@-@@")
End If
Next c
End Sub
================================
--ron
 
Back
Top