separating alpha numeric vlue

[Narasimha]

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

 

[Ron Coderre]

With
A1: (text followed by numbers)

Try this:

B1: =LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-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)

 

[Mike H]

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

 

[Pete_UK]

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

 

[Narasimha]

wow, thanks ron. greatful to you

With
A1: (text followed by numbers)

Try this:

B1: =LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-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]

thanks Pete

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

 

[Narasimha]

thanks alot mike

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]

dear Ron,
if starts with numeric and ends with alpha for example 123MLN , then how?
please suggest for this also
thanks

 

[Narasimha]

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

 

[Narasimha]

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]

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)

 

[Pete_UK]

You're welcome.

Pete

thanks Pete






- Show quoted text -

 

[Rick Rothstein \(MVP - VB\)]

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&"abcdefghijklmnopqrstuvwxyz")),255)

NOTE: Commit this formula by pressing Ctrl+Shift+Enter instead of just
Enter.

Rick

 

[Ron Coderre]

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)

 

[Narasimha]

wonderful,thanks alot Ron.

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]

thanks Rick

Give this array-entered formula a try...

=MID(A1,MIN(SEARCH(CHAR(64+ROW($1:26)),A1&"abcdefghijklmnopqrstuvwxyz")),255)

NOTE: Commit this formula by pressing Ctrl+Shift+Enter instead of just
Enter.

Rick

 

[Bernd P]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

Narasimha, if you are still reading this thread, you might find the
alternate solution I posted to Ron of some interest.

Rick