Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

VBA Strip Leading *

I have cells that are preceded by a *

When I do a replace (ctrl-H) it replaces the entire cell contents.

How can I remove just the preceding * character this using VBA?


Post your answer or comment

comments powered by Disqus
Greetings, I am a VBA rookie who is just starting to understand the VBA language.

I have a listing of numbers in column A and I am trying to write a macro which will automatically change the number to an 11 digit number with leading zeros if they are already not there. I am able to get the code to work on an individual cell (in this example A1), but I do not know how to make it work for the entire column and stop at the last entry in the column. Any help would be appreciated. Here is the code that I am using, Thanks in advance.

Sub test()

Dim Numbers As Long

Numbers = ActiveSheet.Cells(1, 1).Value
With Cells(1, 1)
.NumberFormat = "@"
.Value = Format(Right("00000000000" & Numbers, 11), "@")
End With
End Sub

I've been a long time lurker of OzGrid and I want to thank everyone on these forums for many years of assistance.
This website is a wonderful resource, and the regular posters are amazing.

Alright on to the problem

I have a large dataset (1000's of rows and over 50 columns)
It is has numeric headers and column labels.
All values are numeric
There are no blanks in the set.

My data looks similar to this (with each number in an individual cell, and numbers between 1 and 4000)

A | 0 | 0 | 0 | 3 | 2 | 9 | 6 |
B | 0 | 0 | 0 | 0 | 6 | 6 | 7 |
C | 0 | 0 | 0 | 0 | 0 | 7 | 5 |
D | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
E | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
F | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
G | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
H | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
I | 0 | 0 | 3 | 4 | 5 | 2 | 6 |
J | 0 | 0 | 0 | 6 | 0 | 0 | 7 |
K | 0 | 0 | 0 | 0 | 2 | 0 | 7 |
L | 0 | 0 | 0 | 0 | 0 | 7 | 4 |
M | 0 | 0 | 0 | 0 | 0 | 0 | 7 |
N | 0 | 0 | 0 | 0 | 0 | 0 | 0 |

My hope is to change it to

A | 3 | 2 | 9 | 6 |
B | 6 | 6 | 7 |
C | 7 | 5 |
D | 1 |
I | 5 | 4 | 5 | 2 | 6 |
J | 6 | 0 | 0 | 7 |
K | 2 | 0 | 7 |
L | 7 | 4 |
M | 7 |

My issue is that I need to delete any cells prior to a value that contain 0, but not after the first value
I want to preserve any 0 between other number (as in row I and K above)

So far my code is


	VB:
	
 Test() 
    Dim i As Long, j As Long 
    Dim cell As Range 
    Dim Row As Range 
    Dim TotalRng As Range 
     
     
    Set TotalRng = Application.InputBox("Choose the Total range", "Total Range", Type:=8) 
    Application.ScreenUpdating = False 
     
    For i = TotalRng(TotalRng.Count).Row To TotalRng.Cells(1, 1).Row Step -1 
         
        For j = TotalRng(TotalRng.Count).Column To TotalRng.Cells(1, 1).Column Step -1 
             
            If Cells(i, j).Value = 0 Then Cells(i, j).Delete shift:=xlShiftToLeft 
        Next j 
    Next i 
     
    Application.ScreenUpdating = True 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
And this is where my VBA knowledge ends
I have two questions

How can I get it to go to the next row when it hits the first value?

Is there any way that i can tell it to delete the entire row if it does not find a value anywhere on the row?
This isn't necessary but would help out too.

To: To best darn group of talent I have worked with!! (OK – so it is a suck up – lol)

I have an Excel file that has several modules of VBA code.

This file is placed on an FTP location to enable access by various parties, (this process has worked for over (3) years – same file – same code).

The operation is that a remote individual will copy down the file from the FTP location – modify the file locally – save the file locally – then – drop it back to the FTP site – for the next individual to access at some point in the future.

Within the last week – I have had several instances, (not always), where – somehow – all of the VBA modules and embedded code has been removed – totally.

This acts like it is a firewall issue – however – nothing has changed within the FTP location – nor – (what I am being told) – has anything changed within the local individuals computers whom access this file.

Other than a totally malicious destruction of the VBA code – has anyone/does anyone know of a way for this to happen??

FWIW – I do not believe that any of the individuals whom are using the file – has the technical ability to removes the VBA code, (as easy as that would be) – I am just indicating this to provide you a reference that these individuals are not overly technical.

Any advise would be greatly appreciated!!

Thank You!!

Choppork

Need formula to strip apostrophe for lookup formula to recognize text as a number.

Hi again.

Could anybody possibly point me in the right direction for stripping unwanted text strings from the following output:

Reply[1] from 212.23.2.197: bytes=32 time=16 ms TTL=117
Reply[1] from 212.23.2.197: bytes=32 time=31 ms TTL=117
Reply[1] from 212.23.2.197: bytes=32 time=31 ms TTL=117
Reply[1] from 62.173.67.99: bytes=32 time=31 ms TTL=50
Host not found: www.zenoc.net error 11001
Host not found: www.zenoc.net error 11001
Host not found: www.zenoc.net error 11001
Host not found: www.zenoc.net error 11001
Host not found: www.zenoc.net error 11001
66.246.48.216: request timed out
66.246.48.216: request timed out
66.246.48.216: request timed out
203.98.189.18: request timed out
203.98.189.18: request timed out
Reply[1] from 203.98.189.18: bytes=32 time=687 ms TTL=46

In an ideal world, I'd like to be able to do the following:
Create a few new variables (columns) which include:

--A binary variable for ping response. i.e. 1 if ping reply, 0 otherwise.

--IP address

--time

--TTL

Additionally, the pings are carried out many times for the same URL, as there are duplicates in the list. Is it possible to, say, take an average of the pings for each URL, and report the same one average PING for all identical URLs?

I realise that this is a big ask, as some of these things are probably not possible. The alternative is I go back to the old find and replace function, and autofilter...

Thanks in advance,

Mike

File Format:

URL -- PING_Output
URL1 ping1
URL2 ping2
URL2 ping2 (different result)
URL3 ping3

Excel 2003
Is there a 'Strip' function in Excel 2003 so that I can strip out the
imbedded spaces between words/phrases that exist in one cell and put the
'compressed' result in another cell?
For example, if I have "Business Profile Stage 3" I'd like to 'strip' the
phrase to be 'BusinessProfileStage3". I just need to strip the spaces out,
nothing else (at this time..!!!). [Feature creep still exists!!]
I'm creating program variable names from useable phrases so the I can
understand what I did at a later date.
TIA,
Tom
[PS: Trim strips leading and trailing spaces but not imbedded. Using an
"AT" or "FIND" doesn't do it universally as I never know how many spaces I'll
need. Unless there's a creative way to iteratively find all spaces and
remove them.]

Excel 2003
Is there a 'Strip' function in Excel 2003 so that I can strip out the
imbedded spaces between words/phrases that exist in one cell and put the
'compressed' result in another cell?
For example, if I have "Business Profile Stage 3" I'd like to 'strip' the
phrase to be 'BusinessProfileStage3". I just need to strip the spaces out,
nothing else (at this time..!!!). [Feature creep still exists!!]
I'm creating program variable names from useable phrases so the I can
understand what I did at a later date.
TIA,
Tom
[PS: Trim strips leading and trailing spaces but not imbedded. Using an
"AT" or "FIND" doesn't do it universally as I never know how many spaces I'll
need. Unless there's a creative way to iteratively find all spaces and
remove them.]

Hi

I am new to VBA (have gone through MS training 30 minutes). My requirement is to check that UK post codes are in the correct format.

I want to create a macro for this and need help.

Below is what I have been doing. Notepad is used because copying from Excel to word takes a very long time.

Quantity 50,000
I have been using a Heath Robinson Method as follows:
trim to remove any leading or training spaces then
Copy to Notepad
Copy to word
Remove all extraneous symbols ,./:;’=-+ Etc
Replace double space with single space.
Copy to Notepad
Copy from Notepad to Excel
split using space as the delimiter into two columns 1st part 2nd Part
Take the first part
Count characters
Sort to bring to the top records with too many characters
Check high number of characters There should be no more than 4
Check the low number of characters as this could indicate a space was in the wrong place
Manually check as this could indicate a space.
Copy to Notepad
Copy 1st part to word
Remove digits
Copy to Notepad
Copy from Notepad to Excel
Count characters
Sort to bring to the top records with too many characters
Check high number of characters There should be no more than 2 / 3
In the case of 3 manually look at the original Post Code and leave
Check the low number of characters as this could indicate a space was in the wrong place
Manually check as this could indicate a space.
Revert to word1st part to word
Ctrl Z till the whole first part is visible.
Remove Letters Copy result to Notepad
Copy from Notepad to Excel
Trim
Count characters.
Sort
Manually check if characters are over 2 against initial code.
Repair those which are SW2X to SW2X
Using = & in excel create new column
Copy new column to itself using special paste and value to remove formula.
ETC

Repeat the process on the 2ns part with slightly different criteria but basically the same.

System prone to give bad results because so many manual actions leave bits out etc.

Here are the post code variations:
UK Structure
• A1 2BC
• D34 5EF
• GH6 7IJ
• KL8M 9NO
I want to make a macro which does this for me.

I have located a java solution which is almost correct but not quite

The total length must be 6,7, or 8 characters, a gap (space character) must be included
The inward code, the part to the right of the gap, must always be 3 characters
The first character of the inward code must be numeric
The second and third characters of the inward code must be alpha
The outward code, the part to the left of the gap, can be 2,3, or 4 characters
The first character of the outward code must be alpha
If the outward code is 3 characters then the last character must be numeric (code not written).
If the outward code is 4 characters then the last character must be alpha (code not written).

<SCRIPT LANGUAGE="JavaScript">

<!-- Begin
function postit(){ //check postcode format is valid
test = document.details.pcode.value; size = test.length
test = test.toUpperCase(); //Change to uppercase
while (test.slice(0,1) == " ") //Strip leading spaces
{test = test.substr(1,size-1);size = test.length
}
while(test.slice(size-1,size)== " ") //Strip trailing spaces
{test = test.substr(0,size-1);size = test.length
}
document.details.pcode.value = test; //write back to form field
if (size < 6 || size > 8){ //Code length rule
alert(test + " is not a valid postcode - wrong length");
document.details.pcode.focus();
return false;
}
if (!(isNaN(test.charAt(0)))){ //leftmost character must be alpha character rule
alert(test + " is not a valid postcode - cannot start with a number");
document.details.pcode.focus();
return false;
}
if (isNaN(test.charAt(size-3))){ //first character of inward code must be numeric rule
alert(test + " is not a valid postcode - alpha character in wrong position");
document.details.pcode.focus();
return false;
}
if (!(isNaN(test.charAt(size-2)))){ //second character of inward code must be alpha rule
alert(test + " is not a valid postcode - number in wrong position");
document.details.pcode.focus();
return false;
}
if (!(isNaN(test.charAt(size-1)))){ //third character of inward code must be alpha rule
alert(test + " is not a valid postcode - number in wrong position");
document.details.pcode.focus();
return false;
}
if (!(test.charAt(size-4) == " ")){//space in position length-3 rule
alert(test + " is not a valid postcode - no space or space in wrong position");
document.details.pcode.focus();
return false;
}
count1 = test.indexOf(" ");count2 = test.lastIndexOf(" ");
if (count1 != count2){//only one space rule
alert(test + " is not a valid postcode - only one space allowed");
document.details.pcode.focus();
return false;
}
alert("Postcode Format OK");
return true;
}
// End -->
</script>

Am I asking too much particularly as I am a new member and a newbie generally

All the Best
Jacky Kenna

Hi,
We have csv file automatically generated where rows look like:
"0123","04567","089".
The problem is that when Excel opens this file it puts it into cells like
123,456,89
stripping leading zeros. Is there way to generate csv file so that leading
zeros won't be stripped so it would like
0123,0456,089 when the file is opened in excel.

Thank you

Vadim

I need a formula to remove/delete leading spaces from a cell.

column b contains data imported from anothe application. unfortuantly some
of these cells have padded leading blanks added.

I need to remove these. but 3000+ cells individually will take hours.

any help?

I have a UPC list. Some are more than 12 digits, and some with less than 12 digits. I need to make sure there are 12 digits in each UPC.

I know how to count using LEN, strip leading zeros of those UPCs that are >12 digits using RIGHT.

What I need now is any number with less than 12 digits, such as 000123, add a 4 to the beginning (4000123) and fill in '0's in between the 4 and the short UPC number to make 12 digits, 400000000123. They vary from 1 to 13 digits.

I guess I need a loop of some sort?

Thanks in advance.

I receive a report that has personnel numbers starting with zeros. They show in Excel with an apostrophe at the beginning to tell Excel to consider the number as text. I need the personnel number to show as a text value with the leading zeros (to copy and paste to another program). In the worksheet, I can use
=Text (reference cell,"00000000")
to convert the number to text with the leading zeros and without the apostrophe. I want to select a range and use VBA to automate the process but I can't find the correct macro function anywhere.

Thank you.

Hi:
I searched through some pages of old threads but could not find specifically one solution for performing the subject task on text strings in cells.

I have a lot of Excel files which contain both numerical data and text strings where each cell has been forced to have a leading apostrophe appended to the left side of the text strings and numbers.

How can I strip the leading apostrophe out?

I know the apostrophe is a hidden character, so I can't use Find & Replace.

I would like to figure out some VBA so that I could build this into and automate this via a macro I'm working on.

Is there a way to comprehensively do this for all cells containing text and numbers in a worksheet??? That would be great.

thanks much!
--Tom

If I have several thousand rows of text strings which all leading zeroes and where the number of total characters in the string is consistent, but which have differing amounts of leading zeroes, is there a way to automatically strip zeroes. In other words is there a function which will keep removing zeroes until it comes to a non-zero character?

If not does anyone have alternative ideas?

Thanks!

Tom

Hi,

I have a workbook that someone else created. When I open it up, I get a
message saying macros are disabled because the security level is high and the
macros are not signed. When I go to Tools | Macros, there are no macros
listed. I have also deleted all the VBA that I could. I have also removed
all of the references to MS Query that were originally used on the workbook
(although these were not very easy to find).

I am guessing that I have missed something and that is why I am getting the
message when I open the workbook. I was just wondering if there is a way
that I can forcibly strip a workbook of all macros, VBA, MS Query references,
etc. I just want a dumb workbook with just data.

Thanks,
Paul
--
Paul Kraemer

Hi Guys,

We are working with VBA to find a fast, seamless way to import data from SQL Server 2000 into Excel 2000. But we are needing to strip the user id's and passwords, maybe even the address of the DB from the code due to security issues. Any ideas?

I have an excel file that is distributed within my organization. That file contains certain worksheets that the end-users do not need to see or make changes to, so in the VBA project, I change those files to "VeryHidden". Then I protect the workbook's VBA code by going to Tools->VBAProject- Project Properties-> Protection tab and adding a password. This worked great as it allowed all the other sheets to function as intended, but didn't allow the users to access the sheets that were hidden.

However, we recently upgraded to Excel 2007, and the password protection is stripped whenever the file is saved with the extension .xlsx. I can save it as a "macro-enabled" file with the extension .xlsm, but all that needs to be done to remove the password is to save it with the .xlsx extension again. Is there something that can be done in Excel 2007 to protect VBA code, or am I doing something completely wrong?

This is my first post here, so please let me know if I've done something wrong!

Hi Guys,

I have to submit SIR reports for my work. Now each column header has to have a fixed length field with numeric fields I need to have leading zeros to make the fields up to a set length, 12 for example. and with Alpha fields I need to add trailing spaces to make up the length.

For example as stated above 1234 would become 000000001234, and if the alpha field need to be 10 characters then: Smith would become Smith[][][][][], where [] is a space.

Is it possible to have a VBA code that you simple select the 2nd cell in a column for example D2 (As D1 would obviously have the header and does not need altering) and then a message box asking how many characters need to be added, then whether this is leading or trailing, and only amend the the cells in the column with the selected range.

So basically whatever active cell I choose for a particular column it was add the necessary characters to complete the field requirements.

The reason I ask is that doing this manually is time consuming and laborious task. I know There would need to be at least 3 message boxes 1) to ask how many characters need adding, 2) what character this form takes either 0 or a space, 3) whether this should be leading or trailing.

I would really appreciate some VBA guru to help me out here as my VBA skills are of the bare minimum.

Thanks in advance for any help offered.

In column D of my worksheet some of the cell contents have a varying number
of spaces. How can I removed any leading spaces from those cells with VBA?

There are spaces within the cells (not leading) that I want to keep.

Example below.

Here is what I have now:

ColD
This is the text
Blah Blah Blah
Blah Blah Blah Blah
This is the text

Here is what I'd like to end up with:

ColD
This is the text
Blah Blah Blah
Blah Blah Blah Blah
This is the text

Thanks in advance!

Scott

Need help with vba in Excel. I have a list of zip codes many of which begin with zeroes so I have converted them to text using Excel's built-in Text function: Text(ZipCode,"00000"). When I run the vba command to assign the value of a zip to another cell on the spreadsheet, it converts it to a number, not as text, and thus my routine bombs on subsequent lines of code on a "type mismatch."

ex: Range("NewZip").Value = Range("OldZip").Value

where OldZip is the original zip code formatted as text and NewZip is the cell where the value of the zip code is assigned--with the leading zeroes stripped off. Is there some sort of Text command in vba whereby I could do something like:

Range("NewZip").Value = Text(Range("OldZip).Value,"0000")

you know, kind of like the built-in Excel text command?

Need help with vba in Excel. I have a list of zip codes many of which begin
with zeroes so I have converted them to text using Excel's built-in Text
function: Text(ZipCode,"00000"). When I run the vba command to assign the
value of a zip to another cell on the spreadsheet, it converts it to a
number, not as text, and thus my routine bombs on subsequent lines of code on
a "type mismatch."

ex: Range("NewZip").Value = Range("OldZip").Value

where OldZip is the original zip code formatted as text and NewZip is the
cell where the value of the zip code is assigned--with the leading zeroes
stripped off. Is there some sort of Text command in vba whereby I could do
something like:

Range("NewZip").Value = Text(Range("OldZip).Value,"0000")

you know, kind of like the built-in Excel text command?

I have a spreadsheet that has one macro that brings up a calculator tab if cells within certain ranges are clicked, and another macro that summarizes all the data and deletes unused cells. When this deletion happens, my named ranges get filled up with $REFs, and so I need to re-establish them after the deletion.

In my VBA, any of the first three (long) lines lead to "Application-defined or object-defined error". But when they're commented out, the next three lines don't produce errors. I really don't understand why. Is there some limit to how long/complicated a range can be in VBA? The same ranges were originally set up in Name Manager without any problem.

Thanks in advance!


	VB:
	
 'Sheets("Pricing-Makes-machined").Range("$G$1392:$G$1395,$G$1420:$G$1423,$G$1448:$G$1451,$G$1476:$G$1479,$G$1504:$G$1507,$G$1532:$G$1535,$G$1560:$G$1563,$G$1588:$G$1591,$G$1616:$G$1619,$G$1644:$G$1647,$G$1672:$G$1675,$G$1700:$G$1703,$G$1728:$G$1731,$G$1756:$G$1759,$G$1784:$G$1787,$G$1812:$G$1815,$G$1840:$G$1843,$G$1868:$G$1871,$G$1896:$G$1899,$G$1924:$G$1927,$G$1952:$G$1955,$G$1980:$G$1983,$G$2008:$G$2011,$G$2036:$G$2039,$G$2064:$G$2067,$G$2092:$G$2095,$G$2120:$G$2123").Name
= "MatCostRange2"
 'Sheets("Pricing-Makes-machined").Range("$G$2148:$G$2151,$G$2176:$G$2179,$G$2204:$G$2207,$G$2232:$G$2235,$G$2260:$G$2263,$G$2288:$G$2291,$G$2316:$G$2319,$G$2344:$G$2347,$G$2372:$G$2375,$G$2400:$G$2403,$G$2428:$G$2431,$G$2456:$G$2459,$G$2484:$G$2487,$G$2512:$G$2515,$G$2540:$G$2543,$G$2568:$G$2571,$G$2596:$G$2599,$G$2624:$G$2627,$G$2652:$G$2655,$G$2680:$G$2683,$G$2708:$G$2711,$G$2736:$G$2739,$G$2764:$G$2767,$G$2792:$G$2795").Name
= "MatCostRange3"
Sheets("Pricing-Makes-machined").Range("$G$3000").Name = "MatCostRange" 
Sheets("Pricing-Makes-machined").Range("$G$3000:$G$3001").Name = "MatCostRange2" 
Sheets("Pricing-Makes-machined").Range("$G$3000:$G$3001, $G$3002:$G$3003").Name = "MatCostRange3" 
ActiveWorkbook.Save 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi,

I'm fairly new to VBA, and am running into a problem when trying to export my file as a txt file. I need to gather data that includes Date, Time, Zip code, and a place holder that has leading zeros (its just 00001). I have a program set up to take all my data and organize it the way I want, and format it to what I need...but when I have it export as a txt file, the formatting doesn't carry through.

This is what happens: I have data formatted like so:
For Time- "hhmm;@" so, 5:56PM shows as 1756
For Date - "yyyymmdd", so 5/16/2011 shows as 20110516
For Placeholder - "0000#", and the number is 1, so it shows as 00001
For Zip = "0####" - only because if the zip has a leading zero it got rid of it in the process

When i export as a txt file, it goes back to 5/16/2011 for the date, some crazy numbers for the time, just the 1 for the placeholder, and drops leading zeros for the zip code. Is there a way to hardcode the formatting in VBA so it will stay how I want it when exporting as a txt file?

Any help would be greatly appreciated.

Thanks

Hi Guys,

I am a little stumped on how I can tackle a problem I have. I have a spreadsheet in which users will enter sales leads including the prospective clients postcode if they get it. Managers need to then run a search based on these postcodes to find out who is coming from outside their postcode range and whom is coming from inside their postcode range (PMA).

Main Problem: I need to test a cell on 200 rows to see if a value on each row matches a figure contained in the PMA list. This was easy for a single postcode and I have underlined and highlighted the lines of code used. Need to scroll to the right for the long If statement to find the test in the code. The range in question is from O26:O76 on the sheet entitled 'Cover'.

Sorry if the macro is a little messy I am fairly mediocre at VBA (Self taught / google searching)

Code is now included in the Macro below. Run the 'Lead' macro will show how I currently search for a singular postcode. I am attempting to do the same but testing each postcode on sheet A1 against the postcode list on the Cover worksheet.


No luck finding an answer? You could always try Google.