Remove text chars/Leave numbers in cell

  • Thread starter Thread starter KnowItAll
  • Start date Start date
K

KnowItAll

Hello,
I'm looking to remove all characters and spaces from a cell EXCEPT for
numbers and dashes...

Have column like...
Hard Part 122442-243
Replace 34423-30
PM Worn belt 34423-40
Replace idler 43312-30 and adjusted
1220-0001 Gasket sealer

Desire result...
122442-243
34423-30
34423-40
43312-30
1220-0001

What methods could I look at to accomplish this? Thanks!!!! :confused:
 
One possible way, not thoroughly tested though except for you example
numbers

=SUBSTITUTE(MID(SUBSTITUTE(A1,"-",999999),MATCH(FALSE,ISERROR(1*MID(SUBSTITU
TE(A1,"-",999999),ROW(INDIRECT("1:100")),1)),0),100-SUM(1*ISERROR(1*MID(SUBS
TITUTE(A1,"-",999999),ROW(INDIRECT("1:100")),1))))*1,999999,"-")

where the data starts in A1

entered with ctrl + shift & enter

copy down

Won't work if you have more than 6 digits to the left of the dash

You can also do it in 2 steps, first help column (assume data in A first
help column in B second in C)

In

=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),255)

copy down

in C

=IF(ISNUMBER(RIGHT(B1)+0),B1,LEFT(B1,FIND(" ",B1)-1))

copy down, C will now have your number-number

copy it, do edit>paste special as vallues in place
 
Hi ...,
Is everything text to begin with, or are some already numbers with
formatted dashes. From you sample will assume everything is
text so any digits/numbers extracted will be text and can have leading
zeros and your dashes.

You will need programming to accomplish this, either a subroutine,
or a user defined function (UDF). Instructions to install/use a macro or UDF
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Warning this will extract digits and dashes no matter where they occur
so "Model 302, catalog 32-0005, twin-housings" will produce
"30232-005-" based on what you asked for, since not additional criteria is provided.

Function ExtractDigits_andDashes(aa As String) As String
ExtractDigits_andDashes = "" '2003-10-18 dmcritchie, misc
Dim i As Long
For i = 1 To Len(aa)
If Mid(aa, i, 1) <= "9" And Mid(aa, i, 1) >= "0" _
Or Mid(aa, i, 1) = "-" Then
ExtractDigits_andDashes = ExtractDigits_andDashes & Mid(aa, i, 1)
End If
Next i
End Function

usage:
=ExtractDigits_andDashes(A1)
=personal.xls!ExtractDigits_andDashes(A1)

------ alternative solution ----
Subroutine to make the changes where the content resides:

To remove the parts you don't want from where they sit, you would use a
subroutine, which you can invoke from Alt+F8 after installing. Will
assume that everything is text to start with to greatly speed up execution,
SpecialCells additionally always restricts scope to the used range.

Warning: if you have only digits in a cell and is a number rather than
text the cell will not be processed, but shouldn't make a difference,
except for presentation (alignment), and for sorting.

Sub LeaveDigits_andDashes()
Dim i As Long '2003-10-18 D.McRitchie, misc
Dim aa As String, bb As String, cell As Range
If Intersect(Selection, Selection.SpecialCells(xlConstants, _
xlTextValues)) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual 'pre XL97 xlManual
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
aa = cell.Text
bb = ""
For i = 1 To Len(aa)
If Mid(aa, i, 1) <= "9" And Mid(aa, i, 1) >= "0" _
Or Mid(aa, i, 1) = "-" Then
bb = bb & Mid(aa, i, 1)
End If
Next i
cell.Value = "'" & bb
Next cell
Application.Calculation = xlCalculationAutomatic 'pre XL97 xlAutomatic
Application.ScreenUpdating = True
End Sub

Usage: Select a column where data is to be changed
Alt+F8, select and run the macro
---
BTW, Your question is answered in the newsgroups, not in ExcelTips
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
 
Also, if you download and install the morefunc.xll add-in (from:
http://longre.free.fr/english/index.html), you can have:

=MCONCAT(IF(ISNUMBER(MATCH(MID(A2,INTVECTOR(LEN(A2),1),1),{"0","1","2","3","
4","5","6","7","8","9","-"},0)),MID(A2,INTVECTOR(LEN(A2),1),1),""))

which you need to confirm with control+shift+enter, not just with enter.
 
David McRitchie said:
You will need programming to accomplish this, either a subroutine,
or a user defined function (UDF). . . .
Click to expand...

Untrue. Worksheet-function-only parsing solutions are commonly given in
*.misc and *.worksheet.functions. Odd that you should have missed seeing
them. However, if you're going to screw around with VBA, you might as well
try to do so efficiently.
Function ExtractDigits_andDashes(aa As String) As String
ExtractDigits_andDashes = "" '2003-10-18 dmcritchie, misc
Dim i As Long
For i = 1 To Len(aa)
If Mid(aa, i, 1) <= "9" And Mid(aa, i, 1) >= "0" _
Or Mid(aa, i, 1) = "-" Then
ExtractDigits_andDashes = ExtractDigits_andDashes & Mid(aa, i, 1)
End If
Next i
End Function
Click to expand...

Function foo(s As String) As String
Dim i As Long, j As Long, n As Long
n = Len(s)
i = 1
Do While i <= n And Mid(s, i, 1) Like "[!-0-9]"
i = i + 1
Loop
j = i + 1
Do While j <= n And Mid(s, j, 1) Like "[-0-9]"
j = j + 1
Loop
foo = Mid(s, i, j - i)
End Function

The point here is that the substring is already an intact whole in the
larger string. Building it up by concatenation is worse than pointless.
Also, there is *NEVER* a good reason to check a single character against
several possible values that isn't most efficiently done with a single Like
comparison. While foo(..) may compile to 32 or so bytes more storage than
the other function, it'll use much less temporary memory while running
(concatenation ain't cheap in terms of memory allocation/deallocation). On
the other hand, foo(..) will run circles around the other function.
 
Peo Sjoblom said:
One possible way, not thoroughly tested though except for you example
numbers

=SUBSTITUTE(MID(SUBSTITUTE(A1,"-",999999),MATCH(FALSE,ISERROR(1*
MID(SUBSTITUTE(A1,"-",999999),ROW(INDIRECT("1:100")),1)),0),100-
SUM(1*ISERROR(1*MID(SUBSTITUTE(A1,"-",999999),
ROW(INDIRECT("1:100")),1))))*1,999999,"-")
Click to expand...
....

For parsing, better to define a name like Seq referring to

=ROW(INDIRECT("1:1024"))

Given such a defined name, try the following array formula.

=TRIM(MID(A1,MATCH(TRUE,ISNUMBER(-MID(A1,Seq,2)),0),MATCH(FALSE,
ISNUMBER(-(MID(A1&"#",Seq+MATCH(TRUE,ISNUMBER(-MID(A1,Seq,2)),0),
1)&"0")),0)))

This will return the longest string of decimal digits and interspersed
*single* dashes. No restriction on the length of the substring as long as
the entire string is 1024 characters of fewer.
 
Function ExtractDigits_andDashes(aa As String) As String
ExtractDigits_andDashes = "" '2003-10-18 dmcritchie, misc
Dim i As Long
For i = 1 To Len(aa)
If Mid(aa, i, 1) <= "9" And Mid(aa, i, 1) >= "0" _
Or Mid(aa, i, 1) = "-" Then
ExtractDigits_andDashes = ExtractDigits_andDashes & Mid(aa, i, 1)
End If
Next i
End Function
Click to expand...

Function foo(s As String) As String
Dim i As Long, j As Long, n As Long
n = Len(s)
i = 1
Do While i <= n And Mid(s, i, 1) Like "[!-0-9]"
i = i + 1
Loop
j = i + 1
Do While j <= n And Mid(s, j, 1) Like "[-0-9]"
j = j + 1
Loop
foo = Mid(s, i, j - i)
End Function
Click to expand...
...

Bad, Harlan! Bad! Bad! foo(..) doesn't do exactly the same thing. So make it


Function foo(ByVal s As String) As String
Dim i As Long, n As Long

n = Len(s)

For i = 1 To n
If Mid(s, i, 1) Like "[!-0-9]" Then Mid(s, i, 1) = " "
Next i

foo = Application.WorksheetFunction.Substitute(s, " ", "")
End Function


But might as well get ambitious. The following returns the specified matching
substring from the left (positive 2nd, n, argument - defaulting to the 1st from
the left), the right (negative 2nd, n, argument), an array of all mathcing
substrings in left to right order (zero 2nd, n, argument), "" if there are no
matching substrings, or ">" or "<" for positive or negative n, respectively, if
there are fewer than ABS(n) matching substrings.


Function foobar(ByVal s As String, Optional n As Long = 1) As Variant
Dim i As Long, j As Long, k As Long, m As Long, rv As Variant

If Not s Like "*[-0-9]*" Then
foobar = IIf(n = 0, Array(""), "")
Exit Function
End If

s = s & " "

m = Len(s)
j = 0
k = 0
ReDim rv(1 To Int(m / 2))

For i = 1 To m

If Mid(s, i, 1) Like "[!-0-9]" And j > 0 Then
k = k + 1
rv(k) = Mid(s, j, i - j)
j = 0

ElseIf Mid(s, i, 1) Like "[-0-9]" And j = 0 Then
j = i

End If

Next i

ReDim Preserve rv(1 To k)

If n = 0 Then
foobar = rv 'return an array of all [-0-9] substrings

ElseIf 1 <= n And n <= k Then
foobar = rv(n) 'n_th [-0-9] substring from the left

ElseIf -k <= n And n <= -1 Then
foobar = rv(k + 1 + n) 'ABS(n)_th [-0-9] substring from the right

Else
foobar = IIf(n > 0, ">", "<") 'no n_th or ABS(n)_th [-0-9] substring

End If

End Function
 
Hi Harlan,
The one to pick out the nth occurrence is a good one to keep
around.

What would you do to modify your original User Defined Function
to select the longest string of contiguous digits and dashes
instead of the first string of contiguous digits and dashes.
(choose first among equal longest lengths found)

Function foo(s As String) As String
Dim i As Long, j As Long, n As Long
n = Len(s)
i = 1
Do While i <= n And Mid(s, i, 1) Like "[!-0-9]"
i = i + 1
Loop
j = i + 1
Do While j <= n And Mid(s, j, 1) Like "[-0-9]"
j = j + 1
Loop
foo = Mid(s, i, j - i)
End Function
 
...

For parsing, better to define a name like Seq referring to

=ROW(INDIRECT("1:1024"))

Given such a defined name, try the following array formula.

=TRIM(MID(A1,MATCH(TRUE,ISNUMBER(-MID(A1,Seq,2)),0),MATCH(FALSE,
ISNUMBER(-(MID(A1&"#",Seq+MATCH(TRUE,ISNUMBER(-MID(A1,Seq,2)),0),
1)&"0")),0)))

This will return the longest string of decimal digits and interspersed
*single* dashes. No restriction on the length of the substring as long as
the entire string is 1024 characters of fewer.
Click to expand...
 
Sorry for preceding blank post.

...
...
=TRIM(MID(A1,MATCH(TRUE,ISNUMBER(-MID(A1,Seq,2)),0),MATCH(FALSE,
ISNUMBER(-(MID(A1&"#",Seq+MATCH(TRUE,ISNUMBER(-MID(A1,Seq,2)),0),
1)&"0")),0)))
Click to expand...
...

This includes periods. For decimal digits and dashes, any number of each in any
order, try the array formula

=MID(A1,MATCH(TRUE,FIND(MID(A1,Seq,1),"-0123456789")>0,0),
MATCH(TRUE,ISERROR(FIND(MID(A1&" ",Seq+MATCH(TRUE,FIND(MID(A1,
Seq,1),"-0123456789")>0,0),1),"-0123456789")>0),0))
 
...
...
What would you do to modify your original User Defined Function
to select the longest string of contiguous digits and dashes
instead of the first string of contiguous digits and dashes.
(choose first among equal longest lengths found)
Click to expand...
...


Function foo(s As String) As String
Dim i As Long, j As Long, k As Long, n As Long

j = 0 'unnecessary but pedantic
k = 0 'unnecessary but pedantic
n = Len(s)

Do While i <= n

i = j + 1
Do While i <= n And Mid(s, i, 1) Like "[!-0-9]"
i = i + 1
Loop

j = i + 1
Do While j <= n And Mid(s, j, 1) Like "[-0-9]"
j = j + 1
Loop

If j - i > k Then
k = j - i
foo = Mid(s, i, k)
End If

Loop
End Function
 
Back
Top