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???

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200809/1

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

"adeel via OfficeKB.com" <u32736@uwe wrote in message
news:8a2ab1b1c798b@uwe...
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???

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200809/1

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