I need to break a mixed charater string from a single cell into multiple cells. For Example F62T-L needs to be broken down into seperate columns = F, 62, T, L. The hitch comes with the string K500BB-PS, I need it to break down into K, 500, BB, P and S. The tough one will be the "PS", sometimes it's there, sometimes it's just the P, sometimes it's three characters. As a an added items, the number I would like to convert from text to a number value. I could do all of this by hand, we're only talking 150 to 200 entries, but having it automated would be nice. Appreciate any help.

Is the string always in the form letters|numbers|letters|dash|letters?

--

Vasant

"Paul Holmes" wrote in message
...
I need to break a mixed charater string from a single cell into multiple
cells. For Example F62T-L needs to be broken down into seperate columns =
F, 62, T, L. The hitch comes with the string K500BB-PS, I need it to break
down into K, 500, BB, P and S. The tough one will be the "PS", sometimes
it's there, sometimes it's just the P, sometimes it's three characters. As
a an added items, the number I would like to convert from text to a number
value. I could do all of this by hand, we're only talking 150 to 200
entries, but having it automated would be nice. Appreciate any help.

This is one solution based on your string. I am sure you can adapt it to variations in the string

Sub split(
a = "K500BB-PSD
' Guard against - being last characte
dashExists = InStr(a, "-"
dashExists = dashExists < Len(a
a = Replace(a, "-", ","
For i = 1 To Len(a
If IsNumeric(Mid(a, i, 1)) The
a = Replace(a, Mid(a, i, 1), "," & Mid(a, i, 1)
Exit Fo
End I
Nex
For j = i + 2 To Len(a
If Not IsNumeric(Mid(a, j, 1)) The
a = Left(a, j) & "," & Mid(a, j
Exit Fo
End I
Nex
If dashExists The
While InStrRev(a, ",") < Len(a) -
i = 2 + InStrRev(a, ","
For j = i To Len(a
a = Replace(a, Mid(a, j, 1), "," & Mid(a, j, 1)
Exit Fo
Nex
Wen
End I
MsgBox
End Su

Please chang

a = Left(a, j) & "," & Mid(a, j
to

a = Left(a, j - 1) & "," & Mid(a, j)

Thanks for the help to all. Question 1, There will never be a dash at
the end. Reply 1 & 2. I'll try the subroutine, it also gives me an
informative starting point for improvements or ideas if the format
changes.



*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!