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

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

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

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

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

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

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

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

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

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

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

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 -

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

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

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

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

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

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

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

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

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

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

Hello Rick,

I see. But please allow that I have a different opinion.

Regards,
Bernd

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