Format Text
 

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


Thanks,
Kevin

Format Text
 

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
--
HTH...

Jim Thomlinson


"Tazflerts" wrote:

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


Thanks,
Kevin

Format Text
 

Hi,
you can use

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

"Tazflerts" wrote:

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


Thanks,
Kevin

Format Text
 

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.

"Jim Thomlinson" wrote:

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
--
HTH...

Jim Thomlinson


"Tazflerts" wrote:

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


Thanks,
Kevin