VBA Remove Invalid Range Name Characters

Nov 3, 2008

I have some code that sets named ranges based on sheet names defined by the user.

Issue is, sheet names allow characters that range names do no (i.e. #, - etc).

Is there a relatively simple way to clean a sheet name to allow it to be used as a range name.

Code example below. I am using Replace, but unsure how many characters are invalid. Unfortunately I am referencing a third party workbook, so no control over sheet names.

Sub test()

Dim sh As Worksheet, shnm As String, wb As Workbook
Set wb = ThisWorkbook

For Each nm In wb.Names
nm.Delete
Next

For Each sh In wb.Sheets

If sh.Visible = xlSheetHidden Then GoTo continue:

View 9 Replies


ADVERTISEMENT

Remove 1st X Characters From Text In Range

Mar 2, 2008

I have a column of data; for each line of data I have something like ABCDEEast Anglia, ABCDFFarnborough. The text at the start is standard and all cases of East Anglia will have ABCDE prior to the East Anglia. Is there an easy way [aside from replacing] to loop through 1000 data points and replace the long method with a shorter concise version (i.e. East Anglia only). I have attached what I mean

lowtusmaximus

View 7 Replies View Related

Remove Characters / Spaces After Text For All Cells Within Range

Oct 12, 2013

Initially I'm simply copying a data table from a web page using "Ctrl + A" then "Ctrl + C", and then pasting the data straight onto a new worksheet so I can work with it. (After temporarily re-naming the old sheet)

But I keep finding what looks like double-spaces after some of the important text within the Range of cells I'm working with. I need to be able to select & conditional format the values of the text in some columns of the sheet, so need to loose these trailing spaces.

Unfortunately, it's not consistence as to how many spaces trail the text I need. Sometimes it's only one space, sometimes its two spaces ?

So far, I've had mixed success with a recorded "Replace" code but none of the other codes I have found on forum pages either don't work all or seem to give any consistent results. E;g; TRIM, CLEAN

I suspect my problem is, I do not know how to call the code properly, or trying to work with too large a range ?

The start of my code reads:

Code:

Sheets("Data").Select
Sheets("Data").Name = "Old Data"
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Select
ActiveSheet.Name = "Data"

[Code]...

' At the moment I'm using to select the pasted range I want to work on: Range(Range("C46"), Range("C46").SpecialCells(xlLastCell)).Select

This is where I need a code to work on the new Data sheet and remove all the trailing characters.

MsgBox "All data cleaned successfully !", vbInformation + vbOKOnly, "All Done"

View 3 Replies View Related

Adding Invalid Characters To Cell Reference Names?

Jun 18, 2013

I am currently using cell references to create a depedendent list with data validation. How to make cell references show up with some invalid characters?

For example, one of my cell references is Youngs_Market_Company. However, when pulling up the cell, I want it to be displayed as Young's Market Company.

View 2 Replies View Related

Removing Multiple Invalid Characters For Dependent Data Validation List

Oct 14, 2009

I have a list drop down that is dependent on a first list. The first list has numbers, spaces, and "-" at the beginning I need to get rid of to make it a valid name to reference. The "Substitute" function can't be nested enough times to make this work for me since I have a fairly lengthy list for the independent column which has differing numbers at the beginning. The first two examples of the independent drop down (which would dictate the second dependent column and drop down) are:

00 - Preconstruction
01 - General Conditions

I would like to name these something like "Preconstruction" and "GeneralConditions" for valid naming convention.

Second thought:

If character removal isn't the most efficient or possible at all, is there a combination of reference functions that could make this work? Ultimately I want to use these 2 drop downs for reference functions on a second worksheet.

View 3 Replies View Related

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 View Related

Dynamic Range For Pivot Table :: Error : Range Is Invalid

Oct 28, 2009

I am trying to get to grips with the dynamic ranges for pivot tables. I have named a range data and in the refers to section put:

=OFFSET(Sheet2!A1,0,0,COUNTA(Sheet2!A:A),COUNTA(Sheet2!1:1))

I was hoping that then when i go to data > Pivot table and it asks for the range i could put =Data but it tells me that the range is invalid. I have attached a copy at the bottom.

View 3 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 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

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

Replace / Remove Accented Characters

Aug 15, 2013

I have very large text data which contains accented characters (E.g., "ÑÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóôõöùúûüýÿˆµ" etc.) within words.

Since I have several data sheets, some of which having 500000+ rows, Find and Replace and similar options are not viable.

I wish to replace each of these characters with regular English characters in all sheets. If that is difficult, these must at least be removed from the whole text. note that I need to preserve regular characters that are commonly used, particularly the hyphen (-) and underscore (_).

View 3 Replies View Related







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