TEXT

  • Thread starter Thread starter adeel via OfficeKB.com
  • Start date Start date
A

adeel via OfficeKB.com

I have large number of coding data like this (K4115KBA02WB005) these are 15
digits, what i want is to enter - sign after 10 digit (like K4115KBA02-WB005).
please tell me some short way.
 
I have large number of coding data like this (K4115KBA02WB005) these are 15
digits, what i want is to enter - sign after 10 digit (like K4115KBA02-WB005).
please tell me some short way.
Click to expand...

left(a1,10)&"-"&right(a1,5) should be close. just substitute your cell
for a1.
 
Use a helper column. If your data is in column A (beginning at A1), then in
another column (Z1 for example) enter the following formula and copy down as
needed.

HTH,
Paul
 
If the number of characters should not always be 15, then this code might
work to add the hyphen after the 10th character if one exists.

Sub addHyphen()
'Select Range manually, then run macro to add hyphen after
'the 10th character if it exists.
Dim cell As Range
For Each cell In Selection
If Len(cell.Value) > 10 Then
cell.Value = Left(cell.Value, 10) & "-" & Mid(cell.Value, 11, 99)
Else: cell.Value = "'" & cell.Value
End If
Next cell
End Sub

Vaya con Dios,
Chuck, CABGx3
 
It might have helped if I included the formula.

=LEFT(A1,20)&"-" & RIGHT(A1,LEN(A1)-10)

Regards,
Paul

PCLIVE said:
Use a helper column. If your data is in column A (beginning at A1), then
in another column (Z1 for example) enter the following formula and copy
down as needed.

HTH,
Paul
Click to expand...
 
Back
Top