|
split prefix and suffix
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 |
split prefix and suffix
Can the prefix only be one or two alfphabetics? And what about the suffix?
-- Kind regards, Niek Otten Microsoft MVP - Excel "RayNDM83" wrote in message ... 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 |
split prefix and suffix
There could be one or two alphabets on the prefix and one alphabet or none on
the suffix side. "Niek Otten" wrote: Can the prefix only be one or two alfphabetics? And what about the suffix? -- Kind regards, Niek Otten Microsoft MVP - Excel "RayNDM83" wrote in message ... 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 |
split prefix and suffix
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 -- Don Guillett Microsoft MVP Excel SalesAid Software "RayNDM83" wrote in message ... 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 |
split prefix and suffix
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 "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "RayNDM83" wrote in message ... 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 |
split prefix and suffix
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. "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "RayNDM83" wrote in message ... 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 |
split prefix and suffix
On Wed, 22 Jul 2009 09:49:01 -0700, RayNDM83
wrote: 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 |
split prefix and suffix
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&"0123456 789")),3)&"-"&RIGHT(LEFT(A1,LEN(A1)-(NOT(ISNUMBER(--RIGHT(A1))))),3) -- Rick (MVP - Excel) "RayNDM83" wrote in message ... 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. "Don Guillett" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "RayNDM83" wrote in message ... 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 |
split prefix and suffix
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. "RayNDM83" wrote: 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 |
| All times are GMT +1. The time now is 03:11 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com