hi
small problem. 123abc45 is text. you cannot "format" text. you can only
format numbers. that is where you are getting your type mismatch error.
for text you have to do something like this.
I was have trouble with your ranges so assuming 123abc45 is in a1.......
Sub stringtest()
Dim s As String
s = Range("A1").Value
Range("A2").Value = _
"'" & Left(s, 4) & "<" & Mid(s, 5, 2) & "" & Right(s, 2)
End Sub

regards
FSt1

"Minitman" wrote:

Greetings,

I am passing a string of characters in the format like this: 123abc45

I would like to have it show up after pasting as: 123A <BC-45

The code I am using to paste with now is:

Select Case Target.Column
Case 3
For i = 1 To 118
With ws1_1.Range("InvData" & i)
Select Case i
Case 4 'Cust List data
.Value = _
ws2_1.Range("MCL_Name").Offset(ws1_1 _
.Range("MyRowNum").Value - 1, 60).Value
I tried to wrap the last line with this format:

Format(ws2_1.Range("MCL_Name").Offset(ws1_1 _
.Range("MyRowNum").Value - 1, 60).Value, "#### <##-##")

And get a type mismatch.

How can I get this custom format at the destination cell?

Any help is really appreciated, Thanks.

-Minitman