Remove Last Comma In The Fields
Jul 14, 2008
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!
View 9 Replies
ADVERTISEMENT
Nov 22, 2009
How to remove the first charcter that contains comma, like this :
,001,003,005 and i want the result is --> 001,003,005.
View 4 Replies
View Related
Jan 16, 2007
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
View 9 Replies
View Related
Sep 2, 2009
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 Related
Aug 7, 2009
I 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.
View 9 Replies
View Related
Jan 13, 2010
I need a simple way to extract the comma in 14,656 imported text so I can do a vlookup against 14656.
View 9 Replies
View Related
Dec 1, 2009
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.
View 2 Replies
View Related
Oct 25, 2009
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 ?
View 3 Replies
View Related
Jan 27, 2014
I know that there is a way to remove or add subtotal for different fields, how to add subtotal only for the fields that have more than 1 value? I don't want to to subtotal for anything that has only 1 value.
View 2 Replies
View Related
Mar 19, 2014
Comma separated data on sheet 2, look up info on sheet 1, return comma separated data on sheet 2.
Sample file attached : Book1.xlsx
View 3 Replies
View Related
Feb 8, 2014
CountryHourDataTotalData
Austria - A10Sum of SeiA51CountryHourSum of SeiASum of SeiT
Sum of SeiT4.88Austria - A10514.88
1Sum of SeiA561562.83
[Code]....
left side pivot created in vb 6.0 & right side pivot table created manually in excel.
i want to generated pivot table using vb 6.0 same as right side pivot.
Set PRange = ws1.Range("R1:Y" & finalrow)
Set PTCache = wb.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)
Set PT = PTCache.CreatePivotTable(TableDestination:=ws2.Cells(1, 1),
[Code]....
View 2 Replies
View Related
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")? ´blank´or ´0´would exclude these numbers, i.e. would return "nlgahighstreeten"
View 5 Replies
View Related
Apr 14, 2014
My data in Col. A looks like this:
1,2,3,432
1,2,3,4
1,2,3,43
I wish to get data after the last comma in each row (my data does not have any spaces). So my result would look like this:
432
4
43
I have 2 formula which I feel should do the work:
1. =RIGHT(A1,(FIND(",",A1))) --> However, this is only giving the last 2 characters in the result (including comma). How to fine tune it?
2. =TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",99)),99)) --> works nice and gives exact result. However, this was designed for extracting data from string with spaces, so not sure what trim and substitute would be doing here. How to make it simpler/ reduce its length by chopping off the un-necessary code?
View 6 Replies
View Related
Sep 30, 2012
Some code where i can add a space after each comma in a string?
I would like to do this VBA. How to do this formula as there could be several of commas in the string
View 4 Replies
View Related
Jul 17, 2008
I am working with large sequences of letters and I'm trying to put them into their own columns. I know about the 'text to columns' tool but the problem is my sequences are not delimited by anything. I would have to think the easiest way to accomplish what I want to do is to insert a comma, space or other delimiter between every letter, then use the text to columns feture.
EX. I'm looking for something that does this...
AAAAAA --turns into--> A,A,A,A,A,A or A A A A A A
View 9 Replies
View Related
Jun 4, 2014
Any easy way to convert Smith John to Smith,John? No need to lose the space.
View 5 Replies
View Related
Dec 9, 2013
I have approx 1000 rows in my spreadsheet. Each row contains one column which is a number. What I would like to do is list all these numbers side by side with only a comma seperating them. Just say the first 3 rows have the following numbers 10003, 10056, 100039. I would like to get the numbers to appear in one cell like this: 10003,10056,100039.
View 3 Replies
View Related
Apr 26, 2007
I did post a problem where I have a number like 123456 and I need to have Excel change it so that it puts a comma before the last two digits .. like so: 1234,56
I got a reply where I got the solution to use 0","00 and this works in Excel (using the custom format)
The only problem is that although the number changes in the cell to 1234,56 it doesn´t do so in the FX window and thus when I use the number to multiply it is actually 123456 instead of 1234,56 like it want it to be.
View 13 Replies
View Related
Sep 30, 2009
I have 1 column of people (LASTNAME FIRSTNAME). I was looking for a way to add a comma after the last name instead of just a space. i.e LASTNAME, FIRSTNAME. Is this possible?
View 3 Replies
View Related
Feb 25, 2013
I have a work book that has about 1200 entries. I'd like to grab everything that comes after the last comma or the last period in the cell. Can this be done?
View 4 Replies
View Related
Sep 25, 2008
How to know the position of third comma in the same cell.
View 14 Replies
View Related
Aug 4, 2009
I think this is a really easy question but I can't seem to think of an easy answer. I have 300 cells in one column I want to have combined into one cell, seperated by commas. I know I could just =A1&","&A2&","&... but this is not what I want to spend my afternoon doing. Is there a better way of doing this?
View 3 Replies
View Related
Oct 2, 2011
I've a column which contains data in below format
alves, martine
burraq, joys
ande, kallis
I want to know the LEN of words before the comma, which would be as in this case
5
6
4
I need a formula for this.
View 5 Replies
View Related
Apr 7, 2013
solve this thing.
how can I delete text after a comma.
from this 638264,1 to 638264
another question. this "&" is to join two or more cells. what is the opposite of that function?
A
B
C
1
8768,1
,1
8768
2
1683,34
,34
1683
A-B=C
View 7 Replies
View Related
Aug 16, 2007
I have four columns of data. Column A is for the Model, B is for options, C is for color, and D is for Trim. What I need to do is for each model I need to have the options in column B to be listed down the page without commas. Each 3 code option needs to have it's own cell. In the below example there are 8 models with their options, color, and trim. I cannot have the options mixed up with the other models. Hope this makes more sense.
Here is the raw data ...
View 9 Replies
View Related
Sep 14, 2009
i have many many cells that have text like the following:
A,B,C,D,E,etc...
what i would like is to have it show like the following instead (within the same cell):
A
B
C
D
etc...
i know i can do this using ALT+Enter to make the separation manually....but there are many many cells....can this be done automatically?
also, it is possible that some cells would have only 1 item so in that case, it should be left alone....
View 9 Replies
View Related
Jun 16, 2006
I have a list that contains names in the format
Lastname, first name
But some of the names have no space after the comma - which is what I am trying to achieve here
For example,
Citizen, Joe
Citizen,Joe
I would like to insert a space after every comma in the name, when there is no space. I am looking at a formula based approach to solve this.
View 4 Replies
View Related
Aug 22, 2006
I have a UserForm with different text boxes, everytime I put a number with decimals (ex 100.23) on a Textbox and I want that number to be shown on a excel cell, VBA changes the "dot" for a "comma" so excel understands it as an integer number 100,23 (10023).
View 9 Replies
View Related
Dec 21, 2006
I want to set at my textbox,so that if user enter the number with comma instead of dot,the comma will automatically be changed into my default format(dot).for example,if user type in (86,5),the value will be converted to (86.5). I have read through the relevant previous thread regarding this matter and I wrote the function as below.It happens to be that (86,5) will change to (865.00),not as what it suppose to be.So can anyone correct my function below?
Private Sub txtdiameter_Change()
If txtdiameter = vbNullString Then Exit Sub
If Not IsNumeric(txtdiameter) Then
MsgBox "Numbers Only"
txtdiameter = "86" 'default value'
End If
End Sub
View 9 Replies
View Related
Apr 1, 2008
I am an Excel Novice. I don't know very much at all about how it works, what formulas are or any of its intracacies. My job requires me to take information from an Excel file that is emailed to me.
I need to know in the simplest terms how to take the first column (which is last names) and add a comma to the end of each name (one in each cell) all the way down that column, before I copy and paste them, so they will read "last name comma space first name".
View 8 Replies
View Related
