# separating alpha numeric vlue

N

#### 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

R

#### 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)

M

#### 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

P

#### 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

N

#### Narasimha

wow, thanks ron. greatful to you

Ron Coderre said:
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)

N

#### Narasimha

thanks Pete

Pete_UK said:
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

N

#### Narasimha

thanks alot mike

Mike H said:
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

N

#### Narasimha

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

N

#### 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?
thanks

N

#### 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?
thanks

R

#### Ron Coderre

Try this:

With
A1: (text, with numbers before or after)

B1: =SUBSTITUTE(A1,C1,"")
=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)

P

R

#### Rick Rothstein \(MVP - VB\)

if starts with numeric and ends with alpha for example 123MLN, then how?

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

R

#### 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)

N

#### Narasimha

wonderful,thanks alot Ron.

Ron Coderre said:
Try this:

With
A1: (text, with numbers before or after)

B1: =SUBSTITUTE(A1,C1,"")
=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)

N

#### Narasimha

thanks Rick

Rick Rothstein (MVP - VB) said:
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

B

#### 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

R

#### 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

R

#### 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

R

#### 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