HELP - TO FIND AND REMOVE BITS OF TEXT

eddievegas

New Member
Joined
Apr 7, 2002
Messages
21
have a list of numbers which i need to remove
the text characters from,like
485P6
25M2
29306P3
3249RP108
25AM2
need to remove character codes 65 thru 90
somehow? thanks in advance
This message was edited by eddievegas on 2002-04-08 15:09
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
On 2002-04-08 15:06, eddievegas wrote:
have a list of numbers which i need to remove
the text characters from,like
485P6
25M2
29306P3
3249RP108
25AM2
need to remove character codes 65 thru 90
somehow? thanks in advance
This message was edited by eddievegas on 2002-04-08 15:09

Eddie,

Lets assume that A1:A5 houses the sample you provided:

In B1 enter and copy down:

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

In C1 enter and copy down:

=SUBSTITUTE(A1,SUBSTITUTE(SUBSTITUTE(B1,8,""),9,""),"")+0

If you don't want to have the results to be number-formatted, just drop +0 from the latter formula.

Caveat. It will not work on strings like 45WQ423U.

Aladin
This message was edited by Aladin Akyurek on 2002-04-08 16:30
 
Upvote 0
Hi Eddie,

I assume you mean the ASCII character codes 65-90 that encompass all upper case standard alphabet characters. Here is a bit of code that I wrote to clean up control characters (it turned out to be totally unnecessary as there is a CLEAN function already in Excel), but the serendipitous result is that it should work fine for your problem. Just select the range you want "cleaned" and run the CleanSelectedRange macro. This code should be placed in a standard macro module.

Sub CleanSelectedRange()
' "Cleans" contents of all selected cells on the active worksheet
Dim Cell As Range
For Each Cell In Selection
If Not Cell.HasFormula Then
Cell.Value = CleanString(Cell.Value)
End If
Next Cell
End Sub

Function CleanString(StrIn As String) As String
' "Cleans" a string by removing embedded characters in the ASCII range from 65-90
'This function runs recursively, each call
' removing one embedded character
Dim iCh As Integer
Dim ACh As Integer
CleanString = StrIn
For iCh = 1 To Len(StrIn)
ACh = Asc(Mid(StrIn, iCh, 1))
If 64 < ACh and ACh < 91 Then
'remove designated character
CleanString = Left(StrIn, iCh - 1) & CleanString(Mid(StrIn, iCh + 1))
Exit Function
End If
Next iCh

End Function
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

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