split prefix and suffix

  • Thread starter Thread starter RayNDM83
  • Start date Start date
R

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
 
There could be one or two alphabets on the prefix and one alphabet or none on
the suffix side.
 
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
 
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
 
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.
 
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
Click to expand...


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
 
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
 
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)
 
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.
 
Back
Top