Inserting A Comma
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
ADVERTISEMENT
Sep 30, 2009
I am trying to create a macro that will create a comma and space between every 5th character within a cell. that would be preference #1. What I decided to go with for now, instead, is to try (using the macro recorder) text to columns, fixed width, and do this after every 5th character, and selecting each cell to be formatted as text.
What I am having difficulties with is trying to tell the macro which cells to touch and which ones not to. I do not want the macro to do text to columns and fixed width on simple text. I only want it to do the macro when it is a string of numbers that, during transfer from PDF to excel, have been concatenated into one cell. I also want the macro to do this for infinite number of rows. I have excel 2007.
I am attaching a small excel file with examples of data I am working with. I have saved this excel file as a 97-2003 version for those who do not have 2007. As I stated, if anybody can help me figure out how to do a simple insert comma space every 5th character where information has been concatenated, that would be preference. Also, I am not trying to change anything within column A. Only column B. On the sample file please note B3 - B6. Rows 5 & 6 are fine as they are. Other problem rows include B11 - B13
View 2 Replies
View Related
May 23, 2014
I have several lists of hundreds of emails that I need to list in an email string via Lotus Notes. I need a space and comma in between each name, ex:
john.doe@dm.com, john.doe1@dm.com, john.doe2@dm.com
I am using the below formula manually but it is taking too long. How to automate this? Is there a way to create the space and comma for as long as the list is?
=CONCATENATE(B6,", ",B7,", ",B8,", ",B9,", ",B10,", ",B12,", ",B13,", ",B14,", ",B15,", ",B16,", ",B17)
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
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
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
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
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
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
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
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
Aug 12, 2014
I have been currently working with Data in which involves time over 24 hours and I am trying to find the sum of these numbers.
An example would be the following :
4,889:38:33 +4,711:28:37 + 4,851:11:26 .
Of course, I have removed the comma and changed format, this did correct the issue. However, any way to find the sum with the comma still involved.
View 4 Replies
View Related
Mar 31, 2014
I need a formula (a VBA script won't work, as it also has to work in a google docs version) that concatenates numbers taken from a specific range in a single row with ", " in between, but only if 2 conditions in the particular column those numbers are in are matched.
Let's say, my numbers are in Row 1, from B1 to N1.
My conditional values are in Rows 5 and 6 respectively, also from B to N.
Values in Row 5 are text strings (Names of people), Values in Row 6 are Dates.
I want the numbers from Row 1 to be concatenated, but only if a particular Name and a date from a specific month are in the same column.
Row 1: 1 2 3
Row 5: Name Name Name
Row 6: Date in Feb, Date in March, Date in March
Upon selection of a month and name in a dropdown cell menu, i want the output to look like this:
Name+March -> 2, 3
Name+February -> 1
View 2 Replies
View Related
May 10, 2009
Is it possible to find text listed in a cell, that has sereral options seperated by commas?
Example:
red, green, blue, orange, purple, black (all listed in one cell)
black is or is not found in targeted cell?
View 6 Replies
View Related
Aug 25, 2009
I've just imported a file into a UK version of Excel from a German made datalogger. The decimal points (which should look like this 0.0) all appear as comas (0,0) with the result that Excel can't read them.
View 5 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
Jan 13, 2010
Is there a SUM formula I can place in cell B1 that will sum values I have in A1 expressed as 1,4,6,7. The number I should see in B1 is 18.
View 14 Replies
View Related
