easy way to remove all letters?

joeloveszoe

Board Regular
Joined
Apr 24, 2014
Messages
102
Office Version
  1. 365
Platform
  1. MacOS
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>
PFX84029
ABFBK407
CRD84893
CRD32112
CRD32122
CRD32132
CRD32142
GLW42113
GLW42113BLU
GLW89097BLU
PFX84025GW
PFX32211
GLW28904
PFX50903EE
GLW39624BLA
PFX27895
PFX52374EE
PFX52674EE
PFX01131
GLW84058
GLW41613
PFX27909
GLW42213
GLW28900
PFX27899
PFX89551BLU
GLW55633BLA

<tbody>
</tbody>
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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
 
Upvote 0
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
 
Upvote 0
thanks - good idea but too many cells to do manually =(
any other thoughts?
 
Upvote 0
This can also be done with VBA, a very long formula, or:


Excel 2010
ABCDEFGHIJKLMNOPQRS
1PFX84029 840298848408402840298402984029
2ABFBK407407440407407407
3CRD84893848938848488489848938489384893
4CRD32112321123323213211321123211232112
5CRD32122321223323213212321223212232122
6CRD32132321323323213213321323213232132
7CRD32142321423323213214321423214232142
8GLW42113421134424214211421134211342113
9GLW42113BLU421134424214211421134211342113
10GLW89097BLU890978898908909890978909789097
11PFX84025GW840258848408402840258402584025
12PFX32211322113323223221322113221132211
13GLW28904289042282892890289042890428904
Sheet19
Cell Formulas
RangeFormula
B1=IFERROR(1*MID($A1,COLUMN(A1),1),"")
M1=L1&E1


and just take the last column
 
Last edited:
Upvote 0
you did not have to do it manually, just those 2 operations. It is not perfect but worth a try.
 
Upvote 0

Forum statistics

Threads
1,212,934
Messages
6,110,762
Members
448,295
Latest member
Uzair Tahir Khan

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