find numbers within text
Chris
string.
For example I might have the strings, 123text and
text123text and I want to eliminate the numeric data and
extract only the alpha characters.
Is this possible?
CLR
cells, and then with the Edit > Replace function, (one digit at a
time).....type the digit in the "Find what" box and leave the "Replace with"
box blank......then hit "Replace all"......do this ten times real fast ,
once with each digit, and all your numbers will be gone.......
Vaya con Dios,
Chuck, CABGx3
"Chris" <chris_...@hotmail.com> wrote in message
news:035901c32ed6$fc11f460$a601...@phx.gbl...
Jason Morin
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE
(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE
(A1,7,""),6,""),5,""),4,""),3,""),2,""),1,""),0,"")
Now use this formula:
=SUBSTITUTE(SUBSTITUTE(sub1,9,""),8,"")
HTH
Jason
Atlanta, GA
>.
>
Norman Harker
One way is to split your entries into columns using:
Experiment on a small sample on a new sheet.
Make sure you have enough columns for each character!
Copy your data to a new column or sheet [important as you will lose your
original data entry]
Select that column of data
Data > Text to Columns
Fixed width > Next
Click on each point on the ruler
Next
Finish
Now use a formula:
=VALUE(IF(ISNUMBER(A1),A1,"")&IF(ISNUMBER(B1),B1,"")&IF(ISNUMBER(C1),C1,"")&IF(I
SNUMBER(D1),D1,"")&IF(ISNUMBER(E1),E1,"")&IF(ISNUMBER(F1),F1,"")&IF(ISNUMBER(G1)
,G1,"")&IF(ISNUMBER(H1),H1,"")&IF(ISNUMBER(I1),I1,"")&IF(ISNUMBER(J1),J1,"")&IF(
ISNUMBER(K1),K1,""))
Copy down
This example returns the number element within strings
123text
text123
text123text
Adjust formula according to your data entries but the principle is that after
splitting up your data into separate columns the formula:
IF(ISNUMBER(A1),A1,"")
returns the number if it is a number and an empty string if it is not.
These numbers and empty strings are concatenated using &.
The result of the concatenation is converted to a value by nesting the whole kit
and caboodle in the VALUE function.
--
Regards
Norman Harker MVP (Excel)
Monday: Public Holidays: Whit Monday / Pentecost (Antigua, Austria, Barbados,
Belgium, Denmark, France, Germany, Hungary, Iceland, Luxembourg, Monaco,
Netherlands, Norway, Senegal, St Lucia, Sweden, Switzerland) ; King's Accession
(Jordan); Queen's Birthday Holiday (Australia); Malvinas Day (Argentina)
Tuesday: Public Holidays: Jordan (Army Day), Portugal (National Day).
Sydney, Australia
Excel & Word format Function Lists free to good homes by direct request to:
njha...@optusnet.com.au
"Chris" <chris_...@hotmail.com> wrote in message
news:035901c32ed6$fc11f460$a601...@phx.gbl...
Harlan Grove
CLR's approach is best if you want to do this in-place. If you want to do this
via formula leaving the original data as-is, then Jason's two formula approach
is close, but if you're going to use one defined name, it hard to understand why
you wouldn't use two defined names. That is, define DeleteNumerals_Temp as
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(INDIRECT("R["&RO&"]C["&CO&"]",0),"0",""),"1",""),"2",""),"3",""),"4","")
and DeleteNumerals as
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(DeleteNumerals_Temp,
"5",""),"6",""),"7",""),"8",""),"9","")
Then define RO and CO as the row and column offsets, respectively, that you want
to use (e.g., RO = 0, CO = -1 for one column to the left on the current row),
and enter =DeleteNumerals in each cell to the right of one containing original
data to be processed.
However, if you're willing to use an add-in, specifically, Laurent Longre's
MOREFUNC.XLL (see http://longre.free.fr/english/), then you could use it's
MCONCAT function in the array formula
=MCONCAT(IF(ABS(CODE(MID(X,ROW(INDIRECT("1:"&LEN(X))),1))-52.5)>5,
MID(X,ROW(INDIRECT("1:"&LEN(X))),1),""))
--
1. Don't attach files to postings in this newsgroup.
2. Snip unnecessary text from quoted text. Indiscriminate quoting is wasteful.
3. Excel 97 & later provides 65,536 rows & 256 columns per worksheet. There are
no add-ins or patches that increase them. Need more? Use something else.
Dana DeLouis
Function DeleteNumbers(str As String) As String
'// = = = = = = = = = = = = = = = = = = = = = = =
'// Requires: "Microsoft VBScript Regular Expressions 5.5"
'// = = = = = = = = = = = = = = = = = = = = = = =
With New RegExp
.Global = True: .Pattern = "\d"
DeleteNumbers = .Replace(str, vbNullString)
End With
End Function
Debug.Print DeleteNumbers("123abcd456efgh789ijk012")
returns...
abcdefghijk
--
Dana DeLouis
Windows XP & Office XP
= = = = = = = = = = = = = = = = =
"Chris" <chris_...@hotmail.com> wrote in message
news:035901c32ed6$fc11f460$a601...@phx.gbl...
Myrna Larson
Aladin Akyurek
"Harlan Grove" <hrl...@aol.com> wrote in message
news:z%9Fa.8398$95....@www.newsranger.com...
> "Chris" wrote...
> >I need to find numerical data within an alphanumeric
> >string.
> >
> >For example I might have the strings, 123text and
> >text123text and I want to eliminate the numeric data and
> >extract only the alpha characters.
> >
> >Is this possible?
> However, if you're willing to use an add-in, specifically, Laurent
Longre's
> MOREFUNC.XLL (see http://longre.free.fr/english/), then you could use it's
> MCONCAT function in the array formula
>
> =MCONCAT(IF(ABS(CODE(MID(X,ROW(INDIRECT("1:"&LEN(X))),1))-52.5)>5,
> MID(X,ROW(INDIRECT("1:"&LEN(X))),1),""))
>
=MCONCAT(IF(ABS(CODE(MID(X,INTVECTOR(LEN(X),1),1))-52.5)>5,MID(X,INTVECTOR(L
EN(X),1),1),""))
Stephen Dunn
one more option:
=LEFT(A1,MIN(IF(1-ISERROR(FIND({1,2,3,4,5,6,7,8,9},A1)),
FIND({1,2,3,4,5,6,7,8,9},A1)))-1)&
RIGHT(A1,LEN(A1)-MAX(IF(1-ISERROR(FIND({1,2,3,4,5,6,7,8,9},A1)),
FIND({1,2,3,4,5,6,7,8,9},A1))))
entered as an array formula (hold Ctrl+Shift when pressing Enter) will work
with 123text, text123text, and text123, but will not work with seperated
numbers within the cell.
Steve D.
"Chris" <chris_...@hotmail.com> wrote in message
news:035901c32ed6$fc11f460$a601...@phx.gbl...
Harlan Grove
>Not sure if you would care for this idea, but I like it.
>
>Function DeleteNumbers(str As String) As String
>'// = = = = = = = = = = = = = = = = = = = = = = =
>'// Requires: "Microsoft VBScript Regular Expressions 5.5"
>'// = = = = = = = = = = = = = = = = = = = = = = =
>
> With New RegExp
> .Global = True: .Pattern = "\d"
> DeleteNumbers = .Replace(str, vbNullString)
> End With
>End Function
>
>
>Debug.Print DeleteNumbers("123abcd456efgh789ijk012")
>returns...
>abcdefghijk
But why not make it general? Make the find and replace patterns (and the
instance, too) parameters. Make replacement and instance optional (the former
defaulting to nothing, so deletion, the latter to all instances), and it's a
very useful general function.
'similar to Excel's SUBSTITUTE but using VBScript's perl-like regular
'expressions, and 3rd arg optional, and negative 4th arg R to L matching
'
'Required:
' orig_text - string to search through,
' match_pat - regexp to find,
'Optional:
' replace_pat - replacement pattern, defaults to "", so deletes matched text
' instance - which matched substring to replace or 0 for all (default)
' negative instance number is matched right to left
'
Function Subst( _
orig_text As String, _
match_pat As String, _
Optional replace_pat As String = "", _
Optional instance As Long = 0 _
) As Variant
'-------------------------------
Dim regex As Object, matches As Object, m As Object
Set regex = CreateObject("vbscript.regexp")
regex.Pattern = match_pat
regex.Global = True
If instance = 0 Then
Subst = regex.Replace(orig_text, replace_pat)
Else
Set matches = regex.Execute(orig_text)
If Abs(instance) > matches.Count Then
Subst = orig_text 'matchnum out of bounds - do nothing
Else
If instance < 0 Then instance = matches.Count + instance + 1
Set m = matches.Item(instance - 1)
Subst = Left(orig_text, m.FirstIndex) & _
regex.Replace(m.Value, replace_pat) & _
Right(orig_text, Len(orig_text) - m.FirstIndex - m.Length)
End If
End If
End Function
Now picky points. The statement 'With New RegExp' works in VBA 6 and higher, so
Excel 2000 and higher, but dies in Excel 97. The Subst function above works with
Excel 97 and may even work with Excel 95. Other picky point: make the .Pattern
property "\d+" to use the RegExp engine more efficiently. Final comment: using
colon statement separators makes for less readable code.