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
I copied my data and used the paste value function, pasted it onto another excel sheet and then saved as CSV. Ideally, there should be no formatting on the cells. However, column J threw some commas for which i am unable to understand.
All i want to do is put a control in place via VBA to remove all commas from the sheet.
Is there a VBA code that can perform this function?
For example, i have some data :
123,123567
12,778
9822,1
8267,222223
then, i want to permanently remove the comma, so the result :
123
12
9822
8267
Because if i use format cells -> use 1k separator, the data actually still contain the comma (even if the looks is 123, but in the formula bar, it stated 123,123567).
If someone enters "1,000" in a TextBox, Isnumeric(TextBoxEntry) says True
If I convert it to a value with something like x = TextBoxEntry * 0, the 1,000 becomes 1
I would prefer it to be 1000. Just removing commas would do the trick. How to do it.
i have data exported as CSV from in house system, the problem is that data is separted by commas therefore some of cells have split, i need VBA to remove the commas and bring my data back into correct format. Below is a sample of what the data looks like, real data is 5000 rows of data
PFOLIO A/C CODE ACCT NAME CUR CODE DESCRIPTN NARRATIVE DATE ENTRY DATE CASH VALUE De --------------------------------------------------------------------------------------------------------------------------------------------ACA001AUDCUST JP MorganAUDINT0005 SALE 39727.08 INTECH BAL GR FD C29-Dec-082-Jan-09-27690.57N
Removing Trailing Commas
I have a list (general ledger) GL codes with trailing commas
Example:
123456789,123456722,123789456,,,,,
When I try to use “replace” and replace the multiple commas with nothing it turns my gl codes into a scientific number (1.23456789123456E+26), but my cell is formatted to text.
The other problem is for numbers with a single trailing comma, it would also remove the commas between the GL codes
Is there a function that removes trailing commas, or commas that are not followed by numbers?
is there a way to automate =left(b1,40) and remove commas from the text?
text in the cell would be something like a name for instance john doe, LLC but some names are to long so was using the =left to reduce it to 40 characters and using find/replace for the commas replacing it with a space.
I have a column of contacts. Each contact has data spread out over many rows, most of which are blank. I would like all of the data spread out through the rows to be in one cell, delineated by commas (so I can import into another program).
Concatenating the cells works except that I end up with 20 commas for two strings of info.
Just so you understand I may have this:
Mary red blue yellow purple
Rob blue purple
Trey yellow
and I want it to look like this
Mary red, blue, yellow, purple
Rob blue, purple
Trey yellow
How should I go about this?
We have a spreadsheet of our customer's info, to send to our collection agency; when we export it puts parenthesis around the area code and adds a hyphen in the number.
Also it puts commas in the figures. And I need the slashes gone from the date - is there a simple way to do this?
I tried formatting but it doesn't take out the () or ,
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")? ´blank´or ´0´would exclude these numbers, i.e. would return "nlgahighstreeten"
i have a Macro, its creating unwanted inverted commas at the start and end of the File.
File:
"SunilManual|123456790|DrivingLicence|908567543|Sunil|Manchan
|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w
|w|w|w|w|w|w|w|w|w|w|w|ww|w|w|w|w|w|w|w|w|w|w
|w|w|w|w|w|w|w|w|w|w|55|55||4|SR-1161||1234567890"
Expected File:
|SunilManual|123456790|DrivingLicence|908567543|Sunil|Manchan
|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w|w
|w|w|w|w|w|w|w|w|w|w|w|ww|w|w|w|w|w|w|w|w|w|w
|w|w|w|w|w|w|w|w|w|w|55|55||4|SR-1161||1234567890|
VBA:
Option Explicit
Sub txtt_export()
Dim buf As String, col As Long, r As Long, ws As Worksheet
[Code]....
I have a large spreadsheet, within which i am trying to remove commas from all cells. I get the error 'formula is too long' when I carry out the search. Some of the cells are >1024 characters in length and contain dates, text etc.
View 5 Replies View Relatedlet'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?
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
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, "/*", "")
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?
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
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
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.
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
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 Relatedi 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.
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 RelatedI 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?
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 RelatedI 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 RelatedI'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.
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 RelatedI 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 RelatedI 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.
