|
separating alpha numeric vlue
Hi all,
how do separate alpha numeric value for example I have values like ABC123,AB234 starts with alpha but the length may be 2 or 3 or 4. just I want alpha value in one column and numeric in another column. could anyone help me ? thanks |
separating alpha numeric vlue
With
A1: (text followed by numbers) Try this: B1: =LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789"))-1) C1: =SUBSTITUTE(A1,B1,"") If A1: ABC876 Then B1 returns: ABC C1 returns 876 Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Narasimha" wrote in message ... Hi all, how do separate alpha numeric value for example I have values like ABC123,AB234 starts with alpha but the length may be 2 or 3 or 4. just I want alpha value in one column and numeric in another column. could anyone help me ? thanks |
separating alpha numeric vlue
Hi,
I'm not entirely sure what you mean but if you want to do this Col A Col B Col C ABC123,AB234 ABC,AB 123234 then right click the sheet tab, view code paste this in and run it Sub extractnumbers() Dim RegExp As Object, Collection As Object, RegMatch As Object Dim Myrange As Range, C As Range, Outstring As String For x = 1 To 2 Set RegExp = CreateObject("vbscript.RegExp") With RegExp .Global = True If x = 1 Then .Pattern = "\D" Else .Pattern = "\d" End If End With Set Myrange = ActiveSheet.Range("a1:a100") 'change to suit For Each C In Myrange Outstring = "" Set Collection = RegExp.Execute(C.Value) For Each RegMatch In Collection Outstring = Outstring & RegMatch Next C.Offset(0, x) = Outstring Next Set Collection = Nothing Set RegExp = Nothing Set Myrange = Nothing Next End Sub It will extract a1 - a100 to columns B * C Mike "Narasimha" wrote: Hi all, how do separate alpha numeric value for example I have values like ABC123,AB234 starts with alpha but the length may be 2 or 3 or 4. just I want alpha value in one column and numeric in another column. could anyone help me ? thanks |
separating alpha numeric vlue
Is your numeric always three digits?
If so, you can get the numeric part (as a number) with: =RIGHT(A1,3)*1 (miss off the *1 if you want it as text), and the alpha part with: =LEFT(A1,LEN(A1)-3) Hope this helps. Pete On Dec 8, 12:32 pm, Narasimha wrote: Hi all, how do separate alpha numeric value for example I have values like ABC123,AB234 starts with alpha but the length may be 2 or 3 or 4. just I want alpha value in one column and numeric in another column. could anyone help me ? thanks |
separating alpha numeric vlue
wow, thanks ron. greatful to you
"Ron Coderre" wrote: With A1: (text followed by numbers) Try this: B1: =LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789"))-1) C1: =SUBSTITUTE(A1,B1,"") If A1: ABC876 Then B1 returns: ABC C1 returns 876 Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Narasimha" wrote in message ... Hi all, how do separate alpha numeric value for example I have values like ABC123,AB234 starts with alpha but the length may be 2 or 3 or 4. just I want alpha value in one column and numeric in another column. could anyone help me ? thanks |
separating alpha numeric vlue
thanks alot mike
"Mike H" wrote: Hi, I'm not entirely sure what you mean but if you want to do this Col A Col B Col C ABC123,AB234 ABC,AB 123234 then right click the sheet tab, view code paste this in and run it Sub extractnumbers() Dim RegExp As Object, Collection As Object, RegMatch As Object Dim Myrange As Range, C As Range, Outstring As String For x = 1 To 2 Set RegExp = CreateObject("vbscript.RegExp") With RegExp .Global = True If x = 1 Then .Pattern = "\D" Else .Pattern = "\d" End If End With Set Myrange = ActiveSheet.Range("a1:a100") 'change to suit For Each C In Myrange Outstring = "" Set Collection = RegExp.Execute(C.Value) For Each RegMatch In Collection Outstring = Outstring & RegMatch Next C.Offset(0, x) = Outstring Next Set Collection = Nothing Set RegExp = Nothing Set Myrange = Nothing Next End Sub It will extract a1 - a100 to columns B * C Mike "Narasimha" wrote: Hi all, how do separate alpha numeric value for example I have values like ABC123,AB234 starts with alpha but the length may be 2 or 3 or 4. just I want alpha value in one column and numeric in another column. could anyone help me ? thanks |
separating alpha numeric vlue
thanks Pete
"Pete_UK" wrote: Is your numeric always three digits? If so, you can get the numeric part (as a number) with: =RIGHT(A1,3)*1 (miss off the *1 if you want it as text), and the alpha part with: =LEFT(A1,LEN(A1)-3) Hope this helps. Pete On Dec 8, 12:32 pm, Narasimha wrote: Hi all, how do separate alpha numeric value for example I have values like ABC123,AB234 starts with alpha but the length may be 2 or 3 or 4. just I want alpha value in one column and numeric in another column. could anyone help me ? thanks |
separating alpha numeric vlue
dear Ron,
if starts with numeric and ends with alpha for example 123MLN , then how? please suggest for this also thanks "Ron Coderre" wrote: With A1: (text followed by numbers) Try this: B1: =LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789"))-1) C1: =SUBSTITUTE(A1,B1,"") If A1: ABC876 Then B1 returns: ABC C1 returns 876 Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Narasimha" wrote in message ... Hi all, how do separate alpha numeric value for example I have values like ABC123,AB234 starts with alpha but the length may be 2 or 3 or 4. just I want alpha value in one column and numeric in another column. could anyone help me ? thanks |
separating alpha numeric vlue
I got it with your inspiration for below one also
=RIGHT(A2,COUNT(IF(FIND({0,1,2,3,4,5,6,7,8,9},A2), 1))-1) thanks alot Ron. dear Ron, if starts with numeric and ends with alpha for example 123MLN , then how? please suggest for this also thanks "Ron Coderre" wrote: With A1: (text followed by numbers) Try this: B1: =LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789"))-1) C1: =SUBSTITUTE(A1,B1,"") If A1: ABC876 Then B1 returns: ABC C1 returns 876 Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Narasimha" wrote in message ... Hi all, how do separate alpha numeric value for example I have values like ABC123,AB234 starts with alpha but the length may be 2 or 3 or 4. just I want alpha value in one column and numeric in another column. could anyone help me ? thanks |
separating alpha numeric vlue
Sorry, it doesn't working for repeated numbers i.e 111ML
help me .................................................. ........ I got it with your inspiration for below one also =RIGHT(A2,COUNT(IF(FIND({0,1,2,3,4,5,6,7,8,9},A2), 1))-1) thanks alot Ron. dear Ron, if starts with numeric and ends with alpha for example 123MLN , then how? please suggest for this also thanks "Ron Coderre" wrote: With A1: (text followed by numbers) Try this: B1: =LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789"))-1) C1: =SUBSTITUTE(A1,B1,"") If A1: ABC876 Then B1 returns: ABC C1 returns 876 Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Narasimha" wrote in message ... Hi all, how do separate alpha numeric value for example I have values like ABC123,AB234 starts with alpha but the length may be 2 or 3 or 4. just I want alpha value in one column and numeric in another column. could anyone help me ? thanks |
separating alpha numeric vlue
Try this:
With A1: (text, with numbers before or after) B1: =SUBSTITUTE(A1,C1,"") C1: (in sections for readability) =LOOKUP(99^99,--("0"&MID(A1,MIN( SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")), ROW($1:$10000)))) If A1: ABC876 or A1: 876ABC Then, either way... B1 returns: ABC C1 returns 876 Note: If there are NO numbers in the string, Col_C returns a zero. Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Narasimha" wrote in message ... Sorry, it doesn't working for repeated numbers i.e 111ML help me .................................................. ....... I got it with your inspiration for below one also =RIGHT(A2,COUNT(IF(FIND({0,1,2,3,4,5,6,7,8,9},A2), 1))-1) thanks alot Ron. dear Ron, if starts with numeric and ends with alpha for example 123MLN , then how? please suggest for this also thanks "Ron Coderre" wrote: With A1: (text followed by numbers) Try this: B1: =LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789"))-1) C1: =SUBSTITUTE(A1,B1,"") If A1: ABC876 Then B1 returns: ABC C1 returns 876 Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Narasimha" wrote in message ... Hi all, how do separate alpha numeric value for example I have values like ABC123,AB234 starts with alpha but the length may be 2 or 3 or 4. just I want alpha value in one column and numeric in another column. could anyone help me ? thanks |
separating alpha numeric vlue
You're welcome.
Pete On Dec 8, 1:46 pm, Narasimha wrote: thanks Pete "Pete_UK" wrote: Is your numeric always three digits? If so, you can get the numeric part (as a number) with: =RIGHT(A1,3)*1 (miss off the *1 if you want it as text), and the alpha part with: =LEFT(A1,LEN(A1)-3) Hope this helps. Pete On Dec 8, 12:32 pm, Narasimha wrote: Hi all, how do separate alpha numeric value for example I have values like ABC123,AB234 starts with alpha but the length may be 2 or 3 or 4. just I want alpha value in one column and numeric in another column. could anyone help me ? thanks- Hide quoted text - - Show quoted text - |
separating alpha numeric vlue
if starts with numeric and ends with alpha for example 123MLN, then how?
please suggest for this also Give this array-entered formula a try... =MID(A1,MIN(SEARCH(CHAR(64+ROW($1:26)),A1&"abcdefg hijklmnopqrstuvwxyz")),255) NOTE: Commit this formula by pressing Ctrl+Shift+Enter instead of just Enter. Rick |
separating alpha numeric vlue
It appears from the posts that the numbers may
occur before or after the text. That formula is not durable against trailing numbers .....it returns the entire source string. (Plus, I have this "thing" about avoiding array formulas unless they're absolutely necessary. Seems like nobody I work with ever remembers to C+S+E them.) -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Rick Rothstein (MVP - VB)" wrote in message ... if starts with numeric and ends with alpha for example 123MLN, then how? please suggest for this also Give this array-entered formula a try... =MID(A1,MIN(SEARCH(CHAR(64+ROW($1:26)),A1&"abcdefg hijklmnopqrstuvwxyz")),255) NOTE: Commit this formula by pressing Ctrl+Shift+Enter instead of just Enter. Rick |
separating alpha numeric vlue
wonderful,thanks alot Ron.
"Ron Coderre" wrote: Try this: With A1: (text, with numbers before or after) B1: =SUBSTITUTE(A1,C1,"") C1: (in sections for readability) =LOOKUP(99^99,--("0"&MID(A1,MIN( SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")), ROW($1:$10000)))) If A1: ABC876 or A1: 876ABC Then, either way... B1 returns: ABC C1 returns 876 Note: If there are NO numbers in the string, Col_C returns a zero. Does that help? -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Narasimha" wrote in message ... Sorry, it doesn't working for repeated numbers i.e 111ML help me .................................................. ....... I got it with your inspiration for below one also =RIGHT(A2,COUNT(IF(FIND({0,1,2,3,4,5,6,7,8,9},A2), 1))-1) thanks alot Ron. dear Ron, if starts with numeric and ends with alpha for example 123MLN , then how? please suggest for this also thanks "Ron Coderre" wrote: With A1: (text followed by numbers) Try this: B1: =LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789"))-1) C1: =SUBSTITUTE(A1,B1,"") If A1: ABC876 Then B1 returns: ABC C1 returns 876 Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Narasimha" wrote in message ... Hi all, how do separate alpha numeric value for example I have values like ABC123,AB234 starts with alpha but the length may be 2 or 3 or 4. just I want alpha value in one column and numeric in another column. could anyone help me ? thanks |
separating alpha numeric vlue
thanks Rick
"Rick Rothstein (MVP - VB)" wrote: if starts with numeric and ends with alpha for example 123MLN, then how? please suggest for this also Give this array-entered formula a try... =MID(A1,MIN(SEARCH(CHAR(64+ROW($1:26)),A1&"abcdefg hijklmnopqrstuvwxyz")),255) NOTE: Commit this formula by pressing Ctrl+Shift+Enter instead of just Enter. Rick |
separating alpha numeric vlue
Hello,
I suggest to take =regexpreplace($A1,"(\D*)(\d+)(\D*)","$1$3") for the text part and =regexpreplace($A1,"(\D*)(\d+)(\D*)","$2") for the number part. See http://www.sulprobil.com/html/regexp.html. Regards, Bernd |
separating alpha numeric vlue
It appears from the posts that the numbers may
occur before or after the text. I'm not sure I read it that way. That formula is not durable against trailing numbers ....it returns the entire source string. But, in case you are right, this normally-entered formula will handle any of the possible situations as it will remove the right AND/OR left numerical portions of the contents in A1 leaving the non-numerical left, right or middle text (even if that remaining text contains embedded digits)... =SUBSTITUTE(SUBSTITUTE(A1,LEFT(A1,SUMPRODUCT(--ISNUMBER(--LEFT(A1,ROW(1:99))))),""),RIGHT(A1,SUMPRODUCT(--ISNUMBER(--RIGHT(A1,ROW($1:99))))),"") So, as long as the length of A1 is less than 100 (although that limitation can be changed as required), the formula will return, as an example, ABC if A1 contains either 1234ABC, ABC5678 or 1234ABC5678 (and it will even return AB4C5DE if A1 contains 123AB4C5DE or AB4C5DE6789 or 123AB4C5DE6789). Assuming, however, that the contents of A1 are of the form 123ABC or ABC123, and that the above formula is in B1, then the leading OR trailing digits can be found with this formula... =SUBSTITUTE(A1,B1,"") (Plus, I have this "thing" about avoiding array formulas unless they're absolutely necessary. Seems like nobody I work with ever remembers to C+S+E them.) I tend to avoid them too. When I do end up using one, and then have to edit it for any reason, I almost always finish the editing session by hitting Enter first and then, when I see the error message (or a nonsensical result), slap my forehead and then click back into the formula bar so I can press Ctrl+Shift+Enter to commit it correctly; so I know what you mean.<g Rick |
separating alpha numeric vlue
I suggest to take
=regexpreplace($A1,"(\D*)(\d+)(\D*)","$1$3") for the text part and =regexpreplace($A1,"(\D*)(\d+)(\D*)","$2") for the number part. See http://www.sulprobil.com/html/regexp.html. While it was not part of the OP's indicated requirements, your suggested method will fail to return the correct results if there are any embedded digits within the text portion of the string of text passed into it (for example, A1 containing AB12CD5678) or if the string of text has digits on both sides of the text. However, I do note that, for the OP's stated requirement, your suggested solution will, in fact, properly handle the digits on either the right or left hand side of the text; so it is definitely a valid solution for the OP's stated needs. My own personal preference though, if I were going to use a macro function solution instead of the spreadsheet formula solution I posted earlier, would be to use a more straight-forward VBA function that does not make use of regular expressions in order to get the text part... Function GetTextPart(SourceString As String) As String Dim X As Long For X = 1 To Len(SourceString) If Not IsNumeric(Mid(SourceString, X, 1)) Then GetTextPart = Mid(SourceString, X) Exit For End If Next For X = Len(GetTextPart) To 1 Step -1 If Not IsNumeric(Mid(GetTextPart, X, 1)) Then GetTextPart = Left(GetTextPart, X) Exit For End If Next End Function and then use a simple SUBSTITUTE spreadsheet function to get the digits part. Again, that is a personal preference given I find regular expressions somewhat hard to construct or to read back later on. Rick |
separating alpha numeric vlue
Narasimha, if you are still reading this thread, you might find the
alternate solution I posted to Ron of some interest. Rick "Narasimha" wrote in message ... thanks Rick "Rick Rothstein (MVP - VB)" wrote: if starts with numeric and ends with alpha for example 123MLN, then how? please suggest for this also Give this array-entered formula a try... =MID(A1,MIN(SEARCH(CHAR(64+ROW($1:26)),A1&"abcdefg hijklmnopqrstuvwxyz")),255) NOTE: Commit this formula by pressing Ctrl+Shift+Enter instead of just Enter. Rick |
separating alpha numeric vlue
On Sat, 8 Dec 2007 04:32:00 -0800, Narasimha
wrote: Hi all, how do separate alpha numeric value for example I have values like ABC123,AB234 starts with alpha but the length may be 2 or 3 or 4. just I want alpha value in one column and numeric in another column. could anyone help me ? thanks This Macro will split "selection" into letters and digits in the adjacent columns. You can hard-code "selection" to a given range, or set up the range to step through in various ways. To enter it, <alt-F11 opens the VB Editor. Ensure your project is highlighted in the project explorer window, then Insert/Module and paste the code below into the window that opens. To use it, select your range, then <alt-F8 and run the Macro. ======================================== Option Explicit Sub reExtr() Dim c As Range Dim re As Object Set re = CreateObject("vbscript.regexp") re.Global = True For Each c In Selection re.Pattern = "[^A-Za-z]" c.Offset(0, 1).Value = re.Replace(c.Value, "") re.Pattern = "\D" c.Offset(0, 2).Value = re.Replace(c.Value, "") Next c End Sub ======================================== --ron |
separating alpha numeric vlue
On Sat, 8 Dec 2007 05:26:00 -0800, Mike H
wrote: Hi, I'm not entirely sure what you mean but if you want to do this Col A Col B Col C ABC123,AB234 ABC,AB 123234 then right click the sheet tab, view code paste this in and run it Sub extractnumbers() Dim RegExp As Object, Collection As Object, RegMatch As Object Dim Myrange As Range, C As Range, Outstring As String For x = 1 To 2 Set RegExp = CreateObject("vbscript.RegExp") With RegExp .Global = True If x = 1 Then .Pattern = "\D" Else .Pattern = "\d" End If End With Set Myrange = ActiveSheet.Range("a1:a100") 'change to suit For Each C In Myrange Outstring = "" Set Collection = RegExp.Execute(C.Value) For Each RegMatch In Collection Outstring = Outstring & RegMatch Next C.Offset(0, x) = Outstring Next Set Collection = Nothing Set RegExp = Nothing Set Myrange = Nothing Next End Sub It will extract a1 - a100 to columns B * C Mike Simpler, I think, to just replace the items that don't match. e.g.: re.Pattern = "[^A-Za-z]" c.Offset(0, 1).Value = re.Replace(c.Value, "") re.Pattern = "\D" c.Offset(0, 2).Value = re.Replace(c.Value, "") --ron |
separating alpha numeric vlue
Hello Rick,
I see. But please allow that I have a different opinion. Regards, Bernd |
separating alpha numeric vlue
I see. But please allow that I have a different opinion.
I do and, in thinking about it, I should have posted my macro code as a direct response to the OP rather than as a follow-up to your message. Please accept my apologies for not having done that. Rick |
| All times are GMT +1. The time now is 08:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com