Remove Numbers from Cell with Text

borski88

Board Regular
Joined
Jul 3, 2015
Messages
71
I know this question gets asked a lot, but I haven't seen any answers that match the issue I am having.

I have a string of characters that has numbers and letters in locations.
The location of the letters within the string is not in a consistent location I need to extract only the letter.

Here is an example:

0D10
3A92
0B90
F100
D173
B111
5E61
6A90
E262
B311

<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>
</tbody>

Any help is greatly appreciated.
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,718
Office Version
  1. 2010
Platform
  1. Windows
I have a string of characters that has numbers and letters in locations.
The location of the letters within the string is not in a consistent location I need to extract only the letter.

Here is an example:
0D10
3A92
0B90
F100
D173
B111
5E61
6A90
E262
B311

<colgroup><col style="width:48pt" width="64"> </colgroup><tbody>
</tbody>
Your question is not clear... when you say "extract", do you mean from the cell directly so that the remaining letter appear in the original cell, or do you mean you want to put the extracted letters in the cell next to the original cell?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,718
Office Version
  1. 2010
Platform
  1. Windows
You could use this formula...

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,"")
 

István Hirsch

Well-known Member
Joined
May 16, 2013
Messages
1,634
If the given sample is representative (1 letter , 3 digits), you can give this a try too:

=MID(A1,MATCH(TRUE,MID(A1,{1,2,3,4},1)>="A",0),1)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,402
Messages
5,601,475
Members
414,452
Latest member
Dannysamworth

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
Top