The below function concats a range of cells by csv. How can I get it to remove the last comma in the string when it's finished?
Function SpecialConcatenate(rnge As Range) As String
Dim r As Long, col As Integer
For c = 1 To rnge.Columns.Count
For r = 1 To rnge.Rows.Count
If rnge.Cells(r, c) "" Then
SpecialConcatenate = SpecialConcatenate & _
rnge.Cells(r, c).Value & ","
End If
Next r
Next c
End Function
How to remove the first charcter that contains comma, like this :
,001,003,005 and i want the result is --> 001,003,005.
I'm doing data clean up for a client & have run across data that contains a comma at the end. They've supplied it to me this way & from what I can see it just ends (there is no space after the final comma) IE:
Cheerful, Chipper, Convivial, Delighted, Ecstatic, Elated, Exultant, Pleasant, Pleased, Sparkling, Sunny, Tranquil, Unadorned, Symphony,
The next row may be similar in descriptors but not contain the extraneous comma at the end. IE:
Compassionate, Tender, Calm, Countrified, Priestly, Tranquil, Unadorned, Symphony, Pastoral
Is there a way to remove the final comma in the fields where it's just "dangling" at the end but not remove it from the fields that don't contain the extraneous comma?
Obviously Find & Replace doesn't work because I need the commas to remain throughout the rest of the data. I've searched the boards & have not found a formula that works... I'm going a bit cross eyed at the moment & with thousands of rows to go through, I'd hate to have to remove the ending comma's one at a time!
I need to remove the last character in a cell if it is a comma. I can't remove all commas because there are other commas in the text.
View 2 Replies View RelatedI currently have a macro that imports data from a web-based query then deletes column A from the query. I do this because I am using a VLOOKUP on the data and the VLOOKUP value would be in column B if I didn't.
The website has changed the format and now once column A is deleted, I'm left with a format of FirstName LastName, POS TEAM (i.e. Tom Brady, QB NEP). My VLOOKUP looks for FirstName LastName so I'm getting the "N/A" error now. I need to remove the "," and "POS" and "TEAM" from the imported data. This would be easy enough if the number of characters right of and including the comma was consistent, but it isn't. (Also, text to columns then CONCATENATE won't work because VLOOKUP looks at values, not formula results...)
Now I know the formula =LEFT(A1,FIND(",",A1)-1) works, but how would I incorporate this into the worksheet to keep the values in column A and not add additional columns to throw off my VLOOKUP.
Ideally I'd like a simple macro to add to my current macro to use the above formula, but I'm not sure of the wording.
I need a simple way to extract the comma in 14,656 imported text so I can do a vlookup against 14656.
Is it possible to take a list that is alphabetized in the following manner--Jones, Mary--, convert it to Mary Jones while also removing the comma after the name?
The =TRIM and =MID formulas that I saw in another thread both seem to convert the text, but they also leave the comma at the end.
This is a delima I cannot figure out. I had to create passwords for a website we are building. I have 3000 employee numbers has to be used. So what i did was took the first initial and middle initial and last initial and first 5 of the ID number. I did a comma delimiter to obtain all of the letters and numbers. example: ABC12345
My problem is none of the passwords work because when I imported the letters and numbers into the sheet it looks just like the above. However on review I cut and pasted back to notepad and the data looks like this:
"A B C 12345"
So its adding a tab in the password thats thats a problem, How do I remove this extra white space between each comma delimited digit? without having to manually delete it ?
In filed I have couple of value separated by comma like below:
A1 header1
B1 header2
C1 header3
Audi
592035, 579733, 653749, 579735
20 000
If my macro found that string (always will be separate by ",") should split the string and add rows (= to number of string). The output should be as below:
A1 header1
B1 header2
C1 header3
Audi
592035
20 000
[Code] .....
I have:
Code:
Set sourceWb = ActiveWorkbook
Set ws = sourceWb.Worksheets(1)
Dim LastRow As Long
Dim MY_Split As Variant
LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
[Code] ...........
i'm a complete excel novice, but i think this should be pretty easy for you guys...
i just bought a new satnav system after 4 years with tomtom...
anyway, to get the speed camera database to work with it, i must add a couple of commas at the end of each set of co-ordinates contained within the spreadsheet, like this:
-3.23956,51.52737,1,50,1,67,,
-5.29024,50.22664,1,50,1,67,,
-5.27461,50.23143,1,50,1,247,,
thing is, most of them don't have the 2 commas at the end and just end with the number, like this:
-3.23956,51.52737,1,50,1,67
-5.29024,50.22664,1,50,1,67,,
-5.27461,50.23143,1,50,1,247
therefore, what i need to do is make up a formula in excel to search for a double comma at the end of each set of co-ordinates and if it doesn't already have them, then it should insert them...
i'm guessing for anyone with excel experience, this should be pretty easy to solve...
I have a spreadsheet with a number of columns containing comma delimited strings (years) which I need to check cell by cell and return false if anything apart from the years 2001 to 2008 is found.
For instance, a cell may contain the years (2001, 2005, 2006, 2007, 2008) or (2006, 2007) or (2001, 2004, 2008) or (2004) or any combination of those 8 years.
I thought of using the Split function on each cell and then looping through the resultant array to do a comparison against each of the 8 years but with a large number of delimited strings to check it could be a bit time consuming. Any idea how I could accomplish this more quickly and efficiently either with a formula or VBA?
I need to count how many comma separated elements are in each cell. This is what I have so far,
[Code] .....
The error is Method 'Range" of object'.
I have a worksheet with 17000 rows of words that end in commas in column A, i.e.
Column A
Row 1 = Word 1,
Row 2 = Words 2,
Row 3 - Word 3,
etc...
I need to get this into the following single string format...
Word 1,Word 2,Word 3,etc...
I want to ultimately save it as a .txt file.
I have extracted a string from my address database which goes like name,address1,address2,city,postcode,country
I need to display in Column B as:
name
address1
address2
city
postcode
country
how to do this using VBA.
I have a macro that takes the column of data from D on Sheet 1, pastes it into A on Sheet 3, gets rid of duplicates and then, in B1, combines all the numbers from A into a comma-separated string. The only problem is that it puts a comma at the beginning of the string. Is there something I can do to make that first comma not be there?
View 5 Replies View RelatedI'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'm attempting to extract the Street Names from an Address.
For Example, if given "123 Very Big Street" I'd want to extract "Very Big".
If given, "123 Very Big" I'd want the same answer.
I've written code to do this, with a simple IF statement to identify mathcing last words like "Street" and "Drive".
How can I efficiently do this with a large list of last words? I'm drawing a blank on how to query against an array that would contain all these values.
How can I remove everything to the left of the first space in a String? For exapmle - 'Mr Adam Bill' should become 'Adam Bill'
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 a list of data that populates B2:B2900
This data is often prefixed by a 'reference code' that I wish to be removed.
Now rater than perform this manually aprox 3000 times is there a formula or some VB code that will complete this for me....
eg
The list of data is shown like:
SC7547-05 - Payne, Freda
SC8706-08 - Rungren, Todd
SC8714-05 - Travis, Randy
SC7517-03 - Beach Boys, The
Love Song
Now You're Gone
SC7512-01 - Horton, Johnny
SC8721-15 - Journey
So I wish for the SC7547-05 - to be removed from the first example to just leave Payne, Freda and continue this throughout the list
However if the data is found not to include this code (as in the 5th/6th examples above) leave it alone
So if the code of formula is run for the above the outcome would be....
Payne, Freda
Rungren, Todd
Travis, Randy
Beach Boys, The
Love Song
Now You're Gone
Horton, Johnny
Journey
i would like to remove string "Total" from a list in col A,
Sheet1 AB5How do I remove total Excel tables to the web >> Excel Jeanie HTML 4
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?
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.
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 Relatedmake equation to remove alphabet characters from a text mixed with alphanumeric characters like below.
eg.
1.ASD000234234-1RST to 00234234-1
2.ZYXW2343WE to 2343
3.abcde2023820eiwls to 2023820
like this
I have a list (SIC Codes) and I want to remove the numbers. The numbers range from 2 to 8 deep. The list exists in column B and I want the new list in column C.
01 Agricultural Production Crops
011 Cash Grains
0111 Wheat
0112 Rice
0115 Corn
0116 Soybeans
0119 Cash grains, nec
011901 Pea and bean farms (legumes)
01190101 Bean (dry field and seed) farm
01190102 Cowpea farm
01190103 Lentil farm
01190104 Mustard seed farm
I am using this code to remove a row if string is found, however, I would like to know how can I set it to do the same for all the worksheet in the same excel?
[Code] ....
I'm using excel 2007 and windows vista. I have 2 cells, A1 contains (01,) and B1 contains (01, 08). I want the result in cell C1 to be (01, 08) by calling function trim_sort($A1&$B1). The code gives me an error "invalid qualifier" for s.length & more.
View 4 Replies View RelatedI have a list of about 1,400 numbers ranging in different lengths. What I'm trying to accomplish with a formula is to remove the single letter at the end of each number (not all numbers have them) and in addition to removing all zeros at the beginning of the number (again not all numbers have them).
Here is an example...say I have the following 5 numbers:
8014554
45678456
87451245
0008014554b
0008014554c
And what I need my formula to do provide is the following results instead:
8014554
45678456
87451245
8014554
8014554
So far I have started with the following =LEFT(A1,LEN(A1)-1), but that will always remove the last character (number and letter).