String manipulation from one cell broken up into multiple columns

P

Paul Holmes

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.
 
V

Vasant Nanavati

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

--

Vasant

Paul Holmes said:
I need to break a mixed charater string from a single cell into multiple
Click to expand...
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.
 
A

AA2e72E

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
 
A

AA2e72E

Please chang

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

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

Paul Holmes

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!
 
Top