Separate Alpha and Numberic Characters

R

Rocky0201

Active Member
Joined
Aug 20, 2009
Messages
278
Hi...

I have a string that contains a value of Alpha and Numberic characters. How best can I break the Alpha characters from the Numberic Characters?

Example:

TmpValue ="Test25"

Needed Result"

Array(0) would = Test
Array(1) would = 25

Thanks in advance for the help.
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
What about:

abc123def
123abc
123abc456

...and so on?
 
Upvote 0
Thanks for responding GTO...

The numeric values will always be at the end of the string. they can be zero "0" to "9999".
 
Upvote 0
this should get you started:


Code: 
Sub SplitStr()
    Dim TmpVal As String
    Dim a(1) As Variant
    Dim Pos As Integer
    
    TmpVal = "Test25"
    For Pos = 1 To Len(TmpVal)
        c = Mid$(TmpVal, Pos, 1)
        If (c >= "0") And (c <= "9") Then
            a(0) = Mid$(TmpVal, 1, Pos - 1)
            a(1) = Mid$(TmpVal, Pos)
        End If
    Next Pos
End Sub
 
Upvote 0
Could also try a UDF
Code: 
'***NEW FUNCTION*** by Scheilrn
'user must activate Microsoft vbscript regular expressions 5.5 in Tools / References
'Description: Separates Alphabetic and Numeric Data.  (i.e. Alpha 123, Alpha123)
'             True or 1 will return alphabet, False or 0 will return numeric
Function Sep(txt As String, flg As Boolean) As String
Application.Volatile
With CreateObject("VBScript.RegExp")
    .Pattern = IIf(flg = True, "\d+", "\D+")
    .Global = True
    Sep = .Replace(txt, "")
End With
End Function
then use
Code: 
=Sep(A1,1)   for ALL letters
=Sep(A1,0)   for ALL numbers
 
Upvote 0
Hi all,

I wanted to try a formula, so here's one I am sure can be improved on. If the UDF is more to the liking, please note that both columns get selected when typing in the SPLITCELL function, and confirmed by CSE.

Excel Workbook
ABCDEF
2Test5123Test5123Test5123
3abc2011abc2011abc2011
4NEGNEG
Sheet1


Rich (BB code): 
Option Explicit
    
Function SplitCell(ByVal CellVal As String) As Variant()
Static REX  As Object
Dim Match   As Object
    
    If REX Is Nothing Then Set REX = CreateObject("VBScript.RegExp")
    With REX
        .Global = False
        .Pattern = "([^0-9]*)([0-9]+)"
        If .test(CellVal) Then
            Set Match = .Execute(CellVal)(0)
            SplitCell = Array(Match.submatches(0), Match.submatches(1))
        Else
            SplitCell = Array("NEG", "NEG")
        End If
    End With
End Function

Hope that helps,

Mark
 
Upvote 0
Rocky0201 said:
Thanks for responding GTO...

The numeric values will always be at the end of the string. they can be zero "0" to "9999".
Click to expand...

In that case:

=REPLACE(A2,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1,"")

would suffice for numbers. While:

=LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1)

would take out the text bit.
 
Last edited:
Upvote 0
Rocky0201 said:
Thanks for responding GTO...

The numeric values will always be at the end of the string. they can be zero "0" to "9999".
Click to expand...

GTO said:
Hi all,

I wanted to try a formula, so here's one I am sure can be improved on. If the UDF is more to the liking, please note that both columns get selected when typing in the SPLITCELL function, and confirmed by CSE.

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-RIGHT: #0000ff 2px solid; BORDER-TOP: #0000ff 2px solid; BORDER-LEFT: #0000ff 2px solid; BORDER-BOTTOM: #0000ff 2px solid">Test5123</TD><TD style="BORDER-RIGHT: #0000ff 2px solid; BORDER-TOP: #0000ff 2px solid; BORDER-BOTTOM: #0000ff 2px solid">Test</TD><TD style="BORDER-RIGHT: #0000ff 2px solid; BORDER-TOP: #0000ff 2px solid; BORDER-BOTTOM: #0000ff 2px solid; TEXT-ALIGN: right">5123</TD><TD style="BORDER-RIGHT: #0000ff 2px solid; BORDER-TOP: #0000ff 2px solid; BORDER-BOTTOM: #0000ff 2px solid"> </TD><TD style="BORDER-RIGHT: #0000ff 2px solid; BORDER-TOP: #0000ff 2px solid; BORDER-BOTTOM: #0000ff 2px solid">Test</TD><TD style="BORDER-RIGHT: #0000ff 2px solid; BORDER-TOP: #0000ff 2px solid; BORDER-BOTTOM: #0000ff 2px solid; TEXT-ALIGN: right">5123</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-RIGHT: #0000ff 2px solid; BORDER-LEFT: #0000ff 2px solid; BORDER-BOTTOM: #0000ff 2px solid">abc2011</TD><TD style="BORDER-RIGHT: #0000ff 2px solid; BORDER-BOTTOM: #0000ff 2px solid">abc</TD><TD style="BORDER-RIGHT: #0000ff 2px solid; BORDER-BOTTOM: #0000ff 2px solid; TEXT-ALIGN: right">2011</TD><TD style="BORDER-RIGHT: #0000ff 2px solid; BORDER-BOTTOM: #0000ff 2px solid"> </TD><TD style="BORDER-RIGHT: #0000ff 2px solid; BORDER-BOTTOM: #0000ff 2px solid">abc</TD><TD style="BORDER-RIGHT: #0000ff 2px solid; BORDER-BOTTOM: #0000ff 2px solid; TEXT-ALIGN: right">2011</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-RIGHT: #0000ff 2px solid; BORDER-LEFT: #0000ff 2px solid; BORDER-BOTTOM: #0000ff 2px solid"> </TD><TD style="BORDER-RIGHT: #0000ff 2px solid; BORDER-BOTTOM: #0000ff 2px solid"> </TD><TD style="BORDER-RIGHT: #0000ff 2px solid; BORDER-BOTTOM: #0000ff 2px solid"> </TD><TD style="BORDER-RIGHT: #0000ff 2px solid; BORDER-BOTTOM: #0000ff 2px solid"> </TD><TD style="BORDER-RIGHT: #0000ff 2px solid; BORDER-BOTTOM: #0000ff 2px solid">NEG</TD><TD style="BORDER-RIGHT: #0000ff 2px solid; BORDER-BOTTOM: #0000ff 2px solid">NEG</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B2</TD><TD>{=MID(A2,1,MIN(IF(ISERR(SEARCH("%",SUBSTITUTE(A2,{0,1,2,3,4,5,6,7,8,9},"%"),1)),99,SEARCH("%",SUBSTITUTE(A2,{0,1,2,3,4,5,6,7,8,9},"%"),1)))-1)}</TD></TR><TR><TD>C2</TD><TD>{=MID(A2,MIN(IF(ISERR(SEARCH("%",SUBSTITUTE(A2,{0,1,2,3,4,5,6,7,8,9},"%"),1)),99,SEARCH("%",SUBSTITUTE(A2,{0,1,2,3,4,5,6,7,8,9},"%"),1))),99)}</TD></TR><TR><TD>E2</TD><TD>{=SPLITCELL(A2)}</TD></TR><TR><TD>F2</TD><TD>{=SPLITCELL(A2)}</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Rich (BB code): 
Option Explicit
 
Function SplitCell(ByVal CellVal As String) As Variant()
Static REX  As Object
Dim Match   As Object
 
    If REX Is Nothing Then Set REX = CreateObject("VBScript.RegExp")
    With REX
        .Global = False
        .Pattern = "([^0-9]*)([0-9]+)"
        If .test(CellVal) Then
            Set Match = .Execute(CellVal)(0)
            SplitCell = Array(Match.submatches(0), Match.submatches(1))
        Else
            SplitCell = Array("NEG", "NEG")
        End If
    End With
End Function

Hope that helps,

Mark
Click to expand...
Here's my offering.

Book1
ABC
2Text123123Text
3x00x
4Well99999999Well
Sheet1

This formula entered in B2 to extract the number:

=LOOKUP(10000,--RIGHT(A2,{1,2,3,4}))

This formula entered in C2 to extract the text:

=LEFT(A2,LEN(A2)-LEN(B2))
 
Upvote 0
T. Valko said:
Here's my offering.

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Text123</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">123</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Text</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">x0</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">0</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">x</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Well9999</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right">9999</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Well</TD></TR></TBODY></TABLE>


This formula entered in B2 to extract the number:

=LOOKUP(10000,--RIGHT(A2,{1,2,3,4}))

This formula entered in C2 to extract the text:

=LEFT(A2,LEN(A2)-LEN(B2))
Click to expand...
Hmmm...

I just thought of a potential problem.

Strings that terminate with multiple zeros like:

Text000

If that's not a vaild entry then no problem.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Extract 8 character number from text string
2
Replies
15
Views
274
Fluff
Fluff
Dan Wilson
  • Solved
Help with Identifying Row numbers
2
Replies
15
Views
634
Dan Wilson
Dan Wilson
Keebellah
  • Question
Excel VBA determine amount of characters
Replies
2
Views
188
Keebellah
Keebellah
R
  • Solved
VBA question regarding multiple instances of strings
Replies
8
Views
260
Peter_SSs
Peter_SSs
D
  • Solved
Insert Dashes In Alpha/Numeric Sequence
Replies
9
Views
404
data808
D

Forum statistics

Threads
1,221,527
Messages
6,160,342
Members
451,638
Latest member
MyFlower

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top