easy way to remove all letters?

[joeloveszoe]

hi
i have cells that look like this - what is the fastest way to remove all letters with out using find & remove one letter at a time?
thanks in advance so much for your help
=) happy friday!!!!

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Helvetica}table.t1 {border-collapse: collapse}td.td1 {border-style: solid; border-width: 1.0px 1.0px 1.0px 1.0px; border-color: #cbcbcb #cbcbcb #cbcbcb #cbcbcb ; padding: 0.0px 5.0px 0.0px 5.0px}</style>[TABLE="class: t1"]
<tbody>[TR]
[TD="class: td1"]PFX84029
[/TD]
[/TR]
[TR]
[TD="class: td1"]ABFBK407
[/TD]
[/TR]
[TR]
[TD="class: td1"]CRD84893
[/TD]
[/TR]
[TR]
[TD="class: td1"]CRD32112
[/TD]
[/TR]
[TR]
[TD="class: td1"]CRD32122
[/TD]
[/TR]
[TR]
[TD="class: td1"]CRD32132
[/TD]
[/TR]
[TR]
[TD="class: td1"]CRD32142
[/TD]
[/TR]
[TR]
[TD="class: td1"]GLW42113
[/TD]
[/TR]
[TR]
[TD="class: td1"]GLW42113BLU
[/TD]
[/TR]
[TR]
[TD="class: td1"]GLW89097BLU
[/TD]
[/TR]
[TR]
[TD="class: td1"]PFX84025GW
[/TD]
[/TR]
[TR]
[TD="class: td1"]PFX32211
[/TD]
[/TR]
[TR]
[TD="class: td1"]GLW28904
[/TD]
[/TR]
[TR]
[TD="class: td1"]PFX50903EE
[/TD]
[/TR]
[TR]
[TD="class: td1"]GLW39624BLA
[/TD]
[/TR]
[TR]
[TD="class: td1"]PFX27895
[/TD]
[/TR]
[TR]
[TD="class: td1"]PFX52374EE
[/TD]
[/TR]
[TR]
[TD="class: td1"]PFX52674EE
[/TD]
[/TR]
[TR]
[TD="class: td1"]PFX01131
[/TD]
[/TR]
[TR]
[TD="class: td1"]GLW84058
[/TD]
[/TR]
[TR]
[TD="class: td1"]GLW41613
[/TD]
[/TR]
[TR]
[TD="class: td1"]PFX27909
[/TD]
[/TR]
[TR]
[TD="class: td1"]GLW42213
[/TD]
[/TR]
[TR]
[TD="class: td1"]GLW28900
[/TD]
[/TR]
[TR]
[TD="class: td1"]PFX27899
[/TD]
[/TR]
[TR]
[TD="class: td1"]PFX89551BLU
[/TD]
[/TR]
[TR]
[TD="class: td1"]GLW55633BLA
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Dryver14]

if you are using 2013 or later you can achieve this with flashfill on the data tab, just type in the first 2 numbers next to the column you are looking at press enter then flashfill.
at first it will give the back letters but once you change the first one it will know what you are trying to do

 

[joeloveszoe]

thanks Dryver14 for the direction - can you elaborate further i am not familiar with the flashfill

 

[Dryver14]

say you have your details in column A. Along side it in B type 84029 then the next line type 407 and press enter then go to the data tab and press flashfill

it will give numbers then down to GLW42113BLU when it will guess 42113BLU, change that to 42113 and it will be correct then for the rest of the column

 

[joeloveszoe]

thanks - good idea but too many cells to do manually =(
any other thoughts?

 

[sheetspread]

This can also be done with VBA, a very long formula, or:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	PFX84029				8	4	0	2	9				8	84	840	8402	84029	84029	84029
2	ABFBK407						4	0	7						4	40	407	407	407
3	CRD84893				8	4	8	9	3				8	84	848	8489	84893	84893	84893
4	CRD32112				3	2	1	1	2				3	32	321	3211	32112	32112	32112
5	CRD32122				3	2	1	2	2				3	32	321	3212	32122	32122	32122
6	CRD32132				3	2	1	3	2				3	32	321	3213	32132	32132	32132
7	CRD32142				3	2	1	4	2				3	32	321	3214	32142	32142	32142
8	GLW42113				4	2	1	1	3				4	42	421	4211	42113	42113	42113
9	GLW42113BLU				4	2	1	1	3				4	42	421	4211	42113	42113	42113
10	GLW89097BLU				8	9	0	9	7				8	89	890	8909	89097	89097	89097
11	PFX84025GW				8	4	0	2	5				8	84	840	8402	84025	84025	84025
12	PFX32211				3	2	2	1	1				3	32	322	3221	32211	32211	32211
13	GLW28904				2	8	9	0	4				2	28	289	2890	28904	28904	28904
Sheet19

Cell Formulas
Range	Formula
B1	=IFERROR(1*MID($A1,COLUMN(A1),1),"")
M1	=L1&E1

and just take the last column

 

[Dryver14]

you did not have to do it manually, just those 2 operations. It is not perfect but worth a try.

 

[joeloveszoe]

alrighty - thanks will give it a try! =)

 

[sheetspread]

Scott's function here:

https://www.mrexcel.com/forum/excel-questions/398916-removing-letters-vba.html

works too

Function RemoveAlpha(r As String)
With CreateObject("vbscript.regexp")
    .Pattern = "[A-Z]"
    .IgnoreCase = True
    .Global = True
    RemoveAlpha = .Replace(r, "")
End With
End Function

 

[István Hirsch]

Try this:

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