split prefix and suffix

[RayNDM83]

C22K001
C15K015A
FA28K005A

Hi,
Could someone help me to split the cell and drop the prefixes C and FA as
well as the suffix A? What will be left will read 22K-001, 15K-015 or 28K-005.
Thanks for the help.
Ray

 

[Niek Otten]

Can the prefix only be one or two alfphabetics? And what about the suffix?

 

[RayNDM83]

There could be one or two alphabets on the prefix and one alphabet or none on
the suffix side.

 

[Don Guillett]

edit replace C ""
edit replace k k-
edit replace fa ""
edit replace a ""
or a looping macro
Sub fixstring()
For Each c In Selection
c.replace "K", "K-"
c.replace "FA", ""
c.replace "A", ""
Next
End Sub

 

[RayNDM83]

Hi Don,
My bad, I didn't give more info about the prefixes and suffixes. The prefix
could be a number of different letters. It could be C, F, S, P, T or
combinations of FA or TA. The suffix could be an A, B or C. And the middle
letter could be a K, M or N. What I was thinking of doing is to find a way to
determine if there is one or two letter prefix and separate them from the
cell. After which I can just split the remaining characters by using the LEFT
formula where the numb_chars is 6.
Thanks,
Ray

 

[RayNDM83]

Thank you for your time guys. No need to grind you mind. I just separated
them using several steps available to use. I just thought there would be an
easier way to split them. Your response Don gave me the idea. Thanks.

 

[Ron Rosenfeld]

C22K001
C15K015A
FA28K005A

Hi,
Could someone help me to split the cell and drop the prefixes C and FA as
well as the suffix A? What will be left will read 22K-001, 15K-015 or 28K-005.
Thanks for the help.
Ray

Here's a UDF (user defined function) that should accomplish that.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=SplitPrefixSuffix(A1)

in some cell.

The assumptions:

*Prefix is 0 to n capital letters
followed by
1-n digits
followed by
1-n capital letters
followed by
1-n digits
followed by
*Suffix of 0-n capital letters

*Prefix and suffix are removed.

A hyphen is placed after the middle capital letter(s).

=======================
Option Explicit
Function SplitPrefixSuffix(s As String) As String
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "[A-Z]*(\d+)([A-Z]+)(\d+)[A-Z]*"
SplitPrefixSuffix = re.Replace(s, "$1$2-$3")
End Function
=======================
--ron

 

[Don Guillett]

Another way

Sub fixstring()
For Each c In Selection
For i = 1 To 2
If Mid(c, i, 1) Like "[0-9]" Then
ms = Right(c, Len(c) - 1)
Else
ms = Right(c, Len(c) - 2)
End If
Next i
If Not Right(ms, 1) Like "[0-9]" Then _
ms = Left(ms, Len(ms) - 1)
c.Value = ms
c.replace "K", "K-"
c.replace "L", "L-"
c.replace "M", "M-"
Next c
End Sub

 

[Rick Rothstein]

For future reference, if the first number group is always 2 digits long and
the second number group is always 3 digits long...

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),3)&"-"&RIGHT(LEFT(A1,LEN(A1)-(NOT(ISNUMBER(--RIGHT(A1))))),3)

 

[RayNDM83]

Thank you very much guys. I ran all 3 solutions and they all work perfectly
fine. On Don's solution, I had to add c.Replace "N", "N-" to cover the letter
N on the mid part of the characters. But it work just fine.
Thanks again guys.