Remove Non-alpha Characters From Alphanumerics With Option To Remove Numbers

Aug 8, 2009

I have found a very useful UDF for removing non-alpha characters from strings. (See below, Credit for posting to Stanley D Grom - Ozgrid post Removing Non-alpha Characters From Text).

Option Explicit

Private Function RemoveCharacters(InString As String) As String
Dim intLoopCounter As Integer
Dim intStringLength As Integer
Dim intASCIIVal As Integer
intStringLength = Len(InString)
InString = LCase(InString)
For intLoopCounter = 1 To intStringLength
intASCIIVal = Asc(Mid(InString, intLoopCounter, 1))
If intASCIIVal >= 97 And intASCIIVal <= 122 Then
RemoveCharacters = RemoveCharacters + Mid(InString, intLoopCounter, 1)
End If
Next intLoopCounter
End Function

Two requests:

1. Could the UDF be modified such that any part of a string contained within brackets is also removed (e.g. "NLGA High Street (West-Enfield), EN6" becomes "nlgahighstreeten")?

2. Can an argument be added to the format of the UDF, such that numbers (0 to 9) are either included or excluded (e.g. RemoveCharacters(A1,1) where the argument 1 would include any numbers (0 to 9), so "NLGA2003 High Street (West-Enfield), EN6" becomes "nlga2003highstreeten6")? blankor 0would exclude these numbers, i.e. would return "nlgahighstreeten"

View 5 Replies


ADVERTISEMENT

Remove All Characters But The Numbers

Sep 10, 2009

there is a none VBA way to remove everything from a cell but the numbers.

Example: A, DAVID (002081) becomes 002081

The cell the formula will go in is F4 and the cell its looking at is K4.

View 9 Replies View Related

Excel 2007 :: How To Remove Letter And Leave Numbers And Chinese Characters

Mar 19, 2013

I need a formula to use on MS Excel 2007 to remove the English letters from a cell A1 and return only the numbers(if any) & Chinese characters to B1.

Please refer to the able below.

Example:

A
B

1
so l首先欢迎大家收听我们的科学与烹饪系列
首先欢迎大家收听我们的科学与烹饪系列

2
讲座的最后一次报告 有点难过这是最后一次了
讲座的最后一次报告 有点难过这是最后一次了

[Code]...

View 7 Replies View Related

Alpha String Formula - Remove Duplicates When Starting In Cell Going Downward

Aug 22, 2013

I have a string of letters in cell column L20 through L3019.

The letters are A, B, C, and R . No other letters.

I wanted to find out if it is possible to remove any duplicates when starting in cell L20 going downward.

For example:

L20 = B
L21 = A
L22 = A
L23 = B
L24 = C
L25 = C
L26 = C
L27 = C
L28 = B
L29 = R
L30 = A
L31 = C
L32 = C
L33 = C
L34 = R
L35 = R
L36 = B
L37 = B

In the range L20 through L37 all the letters have appeared so my goal is to create a formula where the outcome is BACR. Starting with the first letter B in cell L20

Cell range L20 through L37. Looking for the next letter which would be either A,C, or R. Since we already used the letter B. In this case it's the letter A
ABCR

Cell range L20 through L37. Looking for the next letter which is the C or R. In this case it's the letter C
CBRA

Finally range L20 through L37. Looking for the letter R which is in cell L29.
RACB

View 9 Replies View Related

Sort Column Of Number &amp; Numbers With Alpha Characters Attached

Jan 7, 2010

Have a spreadsheet that contains a column of 3 digit numbers as well as 3 digit numbers with 2 trailing alpha characters.

Example:

376
377
421
376AB
376XY
377NC
421GQ
421EF

Need to sort by this column, but, with the parameter of sorting first by the numeric only, and then by numeric with alphas. So, the above list would look like this sorted properly:

376
376AB
376XY
377
377NC
421
421EF
421GQ

View 9 Replies View Related

Unable To Remove Option Button

Jun 25, 2009

I have an option button control on a spreadsheet and I can't seem to remove it from the sheet. I can't right click on it, I can't delete the rows it is in.

View 3 Replies View Related

How To Remove Delete Option When Right Click On Sheet Tab

Jun 21, 2014

When right clicking on the sheet tab, I need to know if it is possible to remove the "Delete" from being an option. My workbook is structured to where if a single sheet is deleted, it screws up all my formulas. I tried to just password protect the workbook, but doing that removes the "Rename" function, which I still need.

View 9 Replies View Related

Remove Dotted Line Around Option Button

Sep 7, 2007

I have created a questionnaire with multiple answers in a userform with multipage.

Everything works fine except on about 10 out of my 30 questions, one of the four answers (all opition buttuns) has a fine dotted line around it.

I really have no clue as to where this line comes from. I have tried deleting the option button and replacing it with a new one. This helped for the concerned button but now the dotted line appeared on another answer of the same question! I have done it several times and each time the dotted line simply shifts from one answer to another but never dissapears! The odd thing is that this only concerns 10 questions, the other 20 are fine...

View 5 Replies View Related

Remove/Disable Open File As Read Only Option

Apr 9, 2008

Is there a way to remove the option to open a file as read only when the file is already open by another user? Ideally when a user tries to open a file that is already in use they would get a message to that effect and the only option at that point would be for them to select "notify" when file is available.

View 8 Replies View Related

Remove Certain Characters From A Row

Jun 5, 2008

let's say row 2 has data that looks like

apple (kg), apple (g), orange (kg), orange (g)

it is possible to remove the (kg) and (g) tags so that it'll become
apple, apple, orange, orange

using VB code?

View 9 Replies View Related

Remove Characters

Apr 27, 2006

May I know how to remove character like

1) full stop
2) spacing
3) Dash
4) Hyphen
5) Left and Right Slash

For example:-

016-2733(LS-800E)
MS12-FS4/2M
1/4"GTR

Output:
0162733LS800E
MS12FS42m
14GTR

View 2 Replies View Related

Remove Characters From Right Of String

Dec 26, 2009

I'm trying to remove everything after a specific character in a string.

I.e. change a website address to the hostname

http://www.excelforum.com/newthread.php
http://usa.excelforum.com/forum/new

to

excelforum.com
usa.excelforum.com

I'm using this formula, which strips the http:// and the www., but does not replace the characters after the first remaining "/" as the wildcard is not recognized.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"www.",""), A2,"http://",""), A2, "/*", "")

View 11 Replies View Related

Maco That Will Remove Certain Characters

Feb 19, 2008

I have to manually go through about 9,000 workbooks. In cell E43 of a certain sheet called "list" I have to delete underscores(_) and replace them with a single space. and remove the Rev** after each name

In example: company_name_t45671000_RevA2

Will look like this when I'm done: company name t45671000

Now I've tried to make a Macro that will delete the underscores and the Rev which worked fine except that it replaced the names with the the name that the macro was recored under.

IE: The first sheet I done worked fine when I hit the keyboard shortcut command which was company_name_t45671000_RevA2.

The second workbook sheet of "list" got fixed but had the name of the one I fixed before it: company name t45671000, where it should have been "company name s6743245.

Is there a way around this?

Also sometimes the sheets are protected, is there a way to incorporate "unprotect sheet" when it needs to be unprotected and then after the file has been corrected, re-enable protection again?

View 11 Replies View Related

Remove Characters Before First Non-zero Number

Mar 6, 2013

I always seem to have trouble with the Find() and MID() function when used together. I try to following the syntax but it keeping erroring...

I'm trying to remove all the characters before the first non-zero number.

e.g. ABC263080 becomes 263080
PROGO0123 becomes 123

View 1 Replies View Related

How To Remove The Last X Characters In A Cell

Apr 3, 2008

I need to remove the last x characters if its equal to 0's

see:
101190
101200
101300
102000
102010
102020

should be

10119
1012
1013
102
10201
10202

View 9 Replies View Related

VBA To Remove Characters And Commas

May 26, 2009

i need a macro to do the following,

1. Remove all commas from activeworksheet ( i notice i cant see the commas in excel, but when i open notepad i have commas in empty rows)

2. Remove all characters such as = + # ( ) $ from Column 5

View 9 Replies View Related

Remove Last X Characters From Cells

Feb 10, 2009

I need to remove the last 2 digits from a cell that maybe different lengths. For example:

PL26 7QS
ST20 0AW
LE4 8LF
PE30 3WH
CF31 3AY

View 3 Replies View Related

Remove Last Three Characters In A Cell

Jul 21, 2006

The numerical results in column A need to have the last three characters stripped from the cells. I used the =LEFT formula in adjacent cells to return the results but I am looking for a way to run code to remove these three numbers in each cell from row 1 to 8000 in column A.

View 2 Replies View Related

Remove Unwanted Characters

Nov 9, 2006

i want to convert an excel spreadsheet into a text file, keeping the same format, but when i do so, excel puts " " around the characters, which i don't want. Example: please see the 2 attachments.

e.g. when i convert, i don't want the " " around the commas in the text file.

View 6 Replies View Related

Remove X First Characters In A String

Nov 16, 2006

I have a string like: AAJDGYE030000460. How can I remove the first character in a macro? I need to look at the second,third, and forth character

View 3 Replies View Related

Remove Characters From End Of String Only

Dec 21, 2006

I have a set of data in column a that consists of email addresses. These email addresses all have underscores after them, ie "abc@hotmail.com_______". It will be a different amount of underscores everytime and I don't want underscores to be removed that are actually part of the address. I had been using the find replace function through vba, ie

Range("A:A").Select
Selection.Replace What:="_", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

however this ofcourse removes from actual parts of the email address. Is there a way to do this?

View 6 Replies View Related

Remove All Non Numeric Characters

Oct 16, 2007

The macro I have select 2 columns and 2000 rows. I need a VBA code that will loop through each of these 4000 cells and remove all characters (replace them with blanks) that are not a number, a period or a decimal. Characters from other languages like Chinese, Japanese and Russian should also be removed.

View 3 Replies View Related

Remove Certain Characters From Column

Oct 25, 2007

I am trying to reformat the following data: 10-10-14-1W5 needs to look like this 100101001401W500. The full description is actually 100/10-10-014-01W500 the desired result is without the slashes and dashes. I have tried to add a custom cell format of 00-00-000-00L000. but it will not apply to the existing data.

View 5 Replies View Related

Remove Last X Characters From String

Jan 22, 2008

I've come across multiple times where I have to do this same sort of task, and I don't think I am doing it the most efficient way.

What I need to do is take a variable which holds a string and remove the last 9 characters from it. Don't need to know what the last 9 characters are, all I care about is knowing what the other characters are in the string. I know this can be done through thingslike susbstrings, but I don't think VBA has a substring function.

View 3 Replies View Related

Remove Last 2 Characters From Cells

Jun 17, 2008

i deal with column that has client initials, date of birth and gender, in this format t-b-23/05/72-f however i want to remove the initials and gender(i.e. f or m on the left) and - so that only date of birth remains in same column.

View 3 Replies View Related

Remove Non-alphanumeric Characters From A String

Aug 2, 2006

I am trying to remove all characters that aren't letters or numbers from a string. Is there any way to differentiate between a non-alphanumeric characters and alphanumeric characters? I'm thinking of something like "ISTEXT()" that I could use on one character at a time. Or are there any wildcards I could use in the Replace function?

View 9 Replies View Related

Copy Column But Remove Last Few Characters

Mar 4, 2009

I have a column of data, (10,000 entries), a list of file names basically, e.g:

Mortality Project Executive Summary.pdf
RPP - AA rate and swaps.xls
Commodities.PPT presentation.ppt
Meeting Preparation 20090302.docx

Anywho the point is I want to remove the file extensions (and of course the "."s just before), but some obviously have 4 character extensions, some 3, some 2 etc... Some documents also have "."s in the file name that I do not want to remove, basically just working from the right keep removing until the first "." is removed.
So the final list wants to be:

Mortality Project Executive Summary
RPP - AA rate and swaps
Commodities.PPT presentation
Meeting Preparation 20090302

I know I should VBA it, but I want it all in the one spreadsheet and that's a bit above me. The document has about 30 other columns.

View 3 Replies View Related

Formula To Remove Unwanted Characters

Apr 28, 2009

I am looking for a formula that will remove any unwanted characters in a cell.

For example i may a word or string of words with "-" , " ' " or "," in them and i would like to have these removed.

View 2 Replies View Related

Selectively Remove Characters From Cells?

Jun 11, 2009

my values within column A are separated by a dash. I'd like column B to continuously copy column A with the exception of the characters after the dash. example:

____A_____________B
12345-456_______12345
22456-333_______22456
1553665-45______15553665

How can I format cell B to constantly reproduce this result?

View 4 Replies View Related

Formatting To Remove Specific Characters

Oct 13, 2009

I have several hundred columns of data in this format: |#########|. I need to remove the non-numeric characters on the edges of the numbers so I can manipulate them. Is there an easy way to do this? I do not want to manually remove all of the characters.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved