Insert "-" in already fill cell

[adeel via OfficeKB.com]

I have large amount of cells with values like that; "6XX01LAA AA01" now what
I want is to insert "-" with in this text value for example; "6-XX-01-LAA-AA-
01" how is it possible???

 

[MartinW]

Hi,

With your text in A1 down, put this in B1 and drag down.
=LEFT(A1,1)&"-"&MID(A1,2,2)&"-"&MID(A1,4,2)
&"-"&MID(A1,6,3)&"-"&MID(A1,9,2)&"-"&MID(A1,11,2)

That will work for your example as provided assuming that the
space in the middle of your example is a typo.

HTH
Martin

 

[Héctor Miguel]

hi, adeel !

I have large amount of cells with values like that; "6XX01LAA AA01"
now what I want is to insert "-" with in this text value
for example; "6-XX-01-LAA-AA-01" how is it possible???

you can do it "in-place" with a macro (in one single step)
just revise/adjust/... your real range (address) and modify/adapt
(don't miss Martin's comment regarding "the space")

hth,
hector.

Sub Test()
Dim d As String
With Range("a2:a40")
d = .Address
.Value = Evaluate("transpose(transpose(" & _
"left(" & d & ")&""-""&" & _
"mid(" & d & ",2,2)&""-""&" & _
"mid(" & d & ",4,2)&""-""&" & _
"mid(" & d & ",6,3)&""-""&" & _
"mid(" & d & ",10,2)&""-""&" & _
"right(" & d & ",2)))")
End With
End Sub