Separating Items With Comma
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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Separating The Dates.
I have a form where users can input a Date by utilizing 3 drop down combo boxes. Once they've made their selection that Date gets written to a cell on the worksheet. If for some reason they need to change that Date, they would reopen the form and select a new Day month or year. I would like the three comboboxes to show the D/M/Y previous written to the worksheet. But I would have to split up the date that has since been concatenated.
View Replies!
View Related
Separating Second Occurrence
[data] .... I'm having a problem seperating the prices in Colomn H into three seperate cells C,E & G The first figure in c is easy enough if a little long (any possible tidier solutions?) The second Im not happy with it is clumsy using the fixed lenght figure "5" The last I cant figure out probably because Ive done the second incorrectly! Finally I can not see why figures apear in away team.
View Replies!
View Related
Separating TEXT Into Different Columns
Seperating TEXT in different columns placed at non-fixed location in a cell... I have some data which contains people's name and the places ( i.e Cities) mixed which I need to seperate, however the challenging part there is no consistency in data as the city can appear first in the middle , last or even just the city name in the cell but it needs to be seperated ... In Column B I have the following type of DATA ...
View Replies!
View Related
Separating Information In Cells
I have rows of cells with the following information.. Row 1, Column 1: Address City/State Example: 111 Old Creek Road Stanton, VA 25523 Is there any way to separate the Address and the City/State? I would much rather the spreadsheet be in the following format. Row 1, Column 1: Address Row 1, Column 2: City/State or .. Row 1, Column 1: 111 Old Creek Road Row 1, Column 2: Stanton, VA 25523 I'm just wondering if there's a fast way to do this, or if I have to go in and do it manually - the reason i'm looking for a faster way to do this is because I have approximately 3000 rows to do it to. :shrugs:
View Replies!
View Related
Separating Recurring Names
Is there any formula or formatting that will list in a seperate table all names that occur equal to or more than a specified number of times? eg: I have a list of names(below) that I want excel to go through and automatically place the recurring names in a seperate table on a seperate worksheet. Gus Bob Mike John Gus Nick Mike Gus So Gus and Mike would be listed in the other table on the other worksheet.
View Replies!
View Related
Separating Commas And Parenthesis
I'm trying to separate text that have commas in between. I've got a column that contains commas and a few cells in those columns have commas and bracket. The problem occurs when there are more than two values WITHIN in the bracket that are separated by commas. How can parse the text in such a way where what ever is within the bracket remains in tact? For example: Controls, Motors, Transformers (LVoltage, High Performance, Medium Voltage). The goal is to separate everything before a comma but for Transformers I would like it to remain as 'Transformers (LVoltage, High Performance, Medium Voltage)'.
View Replies!
View Related
Separating Cells In A Worksheet
Set up a worksheet so that it has 2 separate sections in the same worksheet ????? I am wanting to have 2 different sets of information in the same worksheet - and need the columns to be different sizes based on the information i want to put in. Is there any way to have this done - as I know that if you change the column width it does all columns - and i only want to do select columns. For example for section 1 - i would like to have the column widths as follows: A1 = 20 B1 = 40 C1 = 10 D1 = 20 E1 = 40 A3 = 20 B3 = 40 C3 = 10 D3 = 20 E3 = 40 This is where i want to put details such as names, dates and results. Then for section 2 - i would like to have the column width as follows: A6 = 10 B6 = 45 C6 = 15 D6 = 30 This is where i want to have a set of questions to answer.
View Replies!
View Related
Separating Characters By Space
I have following kinds of texts in a column. MISCELLANEOUS EQUIPMENT JBKU348597-3 T-225 75984 I want to convert above texts with space between every character as follows: M I S C E L L A N E O U S E Q U I P M E N T J B K U 3 4 8 5 9 7 - 3 T - 2 2 5 7 5 9 8 4
View Replies!
View Related
Separating The Records Depending On The Value
I have attached 2 spreadsheet :- 1. BounceBackMaster.xls 2. BouncebackMails.xls I need to excute the macro from master file asking the input file. Input file will be the Mails spreadsheet. In the master spreadsheet there will be "TAG" column, depending on value it need to check the value in the Mails spreadsheet "MessageContent" column after subject value. It need to group all tAG value, each sheet need to be created with TAG value and related need to be copied from "BounceBack" sheet to created sheet. Example: Step 1 :- Macro is executed from "BounceBackMaster.xls" and asked for input file, input file will be "BouncebackMails.xls" Step 2 :- From "BounceBackMaster.xls", First TAG value will be readed " Monthly Account Statement" is check in "BouncebackMails.xls"........
View Replies!
View Related
String Separating Single Cell
I am trying to write a simple code for some project management/scheduling spreadsheet in my office. We have projects with multiple people working at one time. I store all names in an array and compare those values to the cells containing the various names. It works when only one person is named, i.e. only [CR], but with multiple people, it doesn't read the data, i.e. [CR, NS, MR], that is separated by commas. Is there a command to recognize the first two characters and compare them to something, then characters, 5&6, and 9&10, so forth....? I tried to use the with characters command that help says will take the "3rd character space and make the font bold" but it doesn't recognize my inequality.
View Replies!
View Related
Separating Macro Command Lines
I have a problem with multiple, similar formulas in a macro. Each of these formulas, depending on the active cell, selects a certain range of cells and prints it. The problem is, no matter which active cell I'm on, the macro activates all lines of the formula and separately prints each range. Is there a way to "separate" these lines in the formula so each range will print separately, depending on the active cell? Here's the
View Replies!
View Related
Separating Rows By Date Range
I need to separate out some rows by date into new worksheets, but I need the dates to be a range. Our cycles are separated into periods, which last about 1 month, so I need one new worksheet for each period. I was thinking about having like a cover sheet that defines the exact dates (like column A has the period title "P1, P2, P3,..." and column B has the start date and column C has the end date). Then on the next sheet is where I would keep my Input data. So I need to separate the rows of the input data sheet out by the periods. The date for each data entry is kept in column M. I have attached a sample workbook explaining what I tried to describe above.
View Replies!
View Related
Separating Weeks In An Order List
I order sushi at work for the office, and I want to automate the reporting side of it this is what the information looks like (bad formatting, sorry) (the notes column is also void currently) date name order # decscript Price Paid? Notes 1/01/2009 John Smith 10 blah 10 5 Y Jane Smith 12 blah 12 7 Y Adam Black 1 blah 1 4 Y Charles Dee 1 blah 1 4 Y 15/01/2009 John Smith 10 blah 10 5 Y Jane Smith 12 blah 12 7 Y Adam Black 1 blah 1 4 Y Charles Dee 1 blah 1 4 Y Handel Fee 6 blah 6 8 Y Gretal Goo 7 blah 7 6 Y 1/02/2009 Adam Black 1 blah 1 4 Y Charles Dee 1 blah 1 4 Y Gretal Goo 9 blah 9 7 Y What I enter is the 1) person's name 2) their order # and 3) wether they have paid (Y/N). The proper version has a vlookup to fill in the description and price. What I want to do (but don't know how) is to automatically count the number of orders each week, and the value of each week's orders. Well, actually I can do the count orders/sum value manually - more specifically I can't work out how to automatically seperate each week. The main problem being there's always an unknown and variable number of orders per week. One solution is to have a fixed number of orders per week which _sort of_ works, but is clunky and inelegant. Plus the names don't autocomplete then as it has blank spaces.
View Replies!
View Related
Auto Separating Groups Of Like Cells
I am working on this project for work that is pretty large (about 13500 rows) that is filled with items that I currently have sorted alphebetically. What I need to do is if there is a group of cells that have the same code, I need to insert a row at the very end of that group and autosum the quantity. It is becoming very time consuming doing it all by hand so I am trying to find out: is there a function that will automatically insert a row after each group of codes?
View Replies!
View Related
Separating Information For Text To Columns
I have a list of 2500 companies. In each cell there is the company name, street address, and phone number. And of course, each one is of varying length. I need a way to put the company name, street address, and phone numbers in seperate columns. I tried going to text to columns but it wouldn't work due to the varying lengths.
View Replies!
View Related
Separating (or Obtaining) The Denominator And Numerator
Is it possible to obtain the numerator and denominator from a fraction? For example, I entered different numbers and display them as fractions in the cell using the format "?/?" Is it possible to obtain the numerator and denominator from that cell and place them into a different cell? ex: In cell [A1] is the value "1/4" can i take the numerator "1" and place it in cell [A2] and the denominator "4" and place it in cell [A3] By the way, I've tried using the functions LEFT and RIGHT but they display the value in it's decimal form. (example: RIGHT(A1, 2) displays the number "25" from 1/4=0.25 as opposed to "/4"
View Replies!
View Related
Separating Different Data That Resides In 1 Column
I have several large excel spreadsheets with different pieces of information that appear in a single column and I am looking for a way to seperate the data into multiple columns. I have read many different articles and how-tos and none seem to help with what I have...here goes: I have a column that has a few thousand lines of info that looks like this: Acme Stores Name: Smith, Steve Reed, Tom ShopRite Stores Name: Stadler, Fred O'Neil, Tim etc. So, its a repetition of the store name, then a cell that just says 'name', followed by the people names associated with that store. I am looking to somehow separate out the store names, leaving just the people's names in that column - but keeping the store names in a seperate column so the people names can be associated with the store name. The only thing that I think might be able to help, is that the 'name' field is constant and its always the cell below the store name. I don't know if that is something that is even remotely helpful here, but thats the only thing I can think of if someone knew some type of conditional command that would pick out a previous cell from one with the word 'name' in it.
View Replies!
View Related
Separating And Converting Strings To Range
I am trying to develope a search macro to go through sheets of a book looking for strings separated by comas in an Inputbox. For example, The data in the inputbox may be inputed as: ahu-1,vav1,erv-2,etc(Number of items may vary). Then to assign variables(range) to each in order to use the "Find" function.
View Replies!
View Related
Separating Info From One Cell Into 2 Different Cells
Whenever I get information from finance.yahoo.com or from my job and put it into excel all the information seems to go in one cell and numerous rows. (I think its b/c I am putting information that isnt' meant to be in excel and forcing it to open up there.) For Example in cell A1 I will have "AIG , AMER INTL GROUP I , 66.08 , 1:16pm , 169.455B , 16.736 , 2.01 , 9.47 , 0.78" Cell A2 I will have GOOG , GOOGLE , 508.9 , 1:21pm , 158.845B , 22.361 , 17.511 , 26.29 , 1 What I want to see is cell A1 as AIG and cell B1 as 66.08 and cell A2 - GOOG and cell B2 508.9. I don't even want the rest of the stuff. Is there any way or formula I can use to just pull the information out of cell and have it separated for me?
View Replies!
View Related
Vba, Separating Ranges With A Border
I have a spreadsheet of data, and all entries are grouped by different unique reference (three digit number). I need something in VBA so if the the unique reference in the columns are the same, to place a border over the ammending data. The actaull; spreadsheet is sorted ascending by the unique references so all the entries are already grouped together. EG - So if I ran the code it would put a border around the group of rows for unique reference 126. Unique reference 126 126 126 126 128 128 I attempted using if activecell offset, but i dont think the logic works. I ran a macro for the border, it is below. Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With.........................
View Replies!
View Related
Separating Text In One Cell Into Individual Cells
I have a spreadsheet which needs formatting I was wondering if anyone would be able to help with creating a macro to do so. The problem with raw report is that in cell A47 there are five columns worth of data in that one cell, then in B48 there are another two, in b49 and b50 one respectively. I manually format it by first joining the separate cells using
View Replies!
View Related
Advanced Concatenate With Spaces Separating Numbers
I am using concatenate and it looks something like this. =CONCATENATE(C6,B6,B7,B8,B9), then I go in and add " ", to add spaces between each number so it looks like this =CONCATENATE(C6," ",B6," ",B7," ",B8," ",B9). Sometimes there are 100 or more cells I am selecting, I would like to use shift, but then it looks like this =CONCATENATE(B4:B127), how do I add spaces to a range? my problem is very similar to the question asked in this thread http://www.excelforum.com/excel-work...ncatenate.html but I am using numbers and would like to separate them with spaces.
View Replies!
View Related
Separating Numeric And Alpha Cell Contents
I have a set of text strings that have some number of alpha characters followed by some number of numeric characters. I need to separate the data into two cells. Example in A1-A5 ABC123456 AB12345 A123456 AA12345 DEF123456 Desired results in B1 - C5 ABC 123456 AB 12345 A 123456 AA 12345 DEF 123456
View Replies!
View Related
Inserting A Comma
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 Replies!
View Related
Cell Changes Decimal To Comma
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 Replies!
View Related
Comma Separated Data
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 Replies!
View Related
Remove The Last Comma In The String
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 Replies!
View Related
Puts A Comma Before The Last Two Digits
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 Replies!
View Related
Space After Comma In Names
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 Replies!
View Related
Separating Code Creates: Run-time Error '424'
I've written some code that fires on a worksheet_change event. It worked fine for the initial bit of code I wrote, but then once I repeated it for several different ranges, I got an error stating COMPILE ERROR: PROCDEURE TOO LARGE. (This amount of code below works fine, it's only when I add the rest it becomes too large.) Private Sub Worksheet_Change(ByVal Target As Range) 'Unprotect Sheet ActiveSheet.Unprotect Password:="clemson" 'Freeze screen Application.ScreenUpdating = False 'Turn Off Auto Calc With Application .Calculation = xlManual .MaxChange = 0.001 End With ' Master Bath Shower Listello If Not Intersect(Target, Range("Listello1ShowerMasterBath")) Is Nothing Then If UCase(Target.Value) = "NONE" Then Range("Listello_1_Options_Shower_Master_Bath").EntireRow.Hidden = True: Range("Listello_2_Shower_Master_Bath").EntireRow.Hidden = True:..........
View Replies!
View Related
Replace Blank Space With Comma
I have the following code which is save Sheet as a text file. I know there are some options in Excel for saving, but it is not saving as ".txt" file. I would like to modify this code to replace blank space with comma between Cells. PHP Private Sub CommandButton1_Click() Application.DisplayAlerts = False ' Save file name and path into a variable template_file = ActiveWorkbook.FullName ' Default directory would be c:temp. Users however will have the ability to change where to save the file if need be. ' Notice that i'm only allowing the save as option to be of .txt format. ' I'm also attaching the current date to the file name............
View Replies!
View Related
Match Function To Find Name With Comma
I have a spreadsheet in which I use a match function to find the row a name is on. The name may be listed several times and the name has a comma in it, for example, "Tom, George". I got it to work once, but it doesn't always work. Is it because of the comma or because it is in the lookup several times? It's my understanding the Excel will return the row number for the first time it sees the name in the list, which is what I want.
View Replies!
View Related
Text File But Comma Delimited
In my workbook I have 10 columns with data, starting at A12 and down. This could be several hundred rows. I would like to export the first three columns only (Column A, B and C). The TXT file should have the following: First line: "This file was exported from Excel" Second line: The value of cell B4 of the worksheet Third line: The value of cell B5 of the worksheet Fourth line: Today's date ( as 27/Sep/2007) Fifth line: No entries (Empty row) Sixth line will be the first numeric transfer. This is cell A12 value, cell B12 value and cell C12 value. Seventh line: Cell A13 value, cell B13 value and cell c13 value. etc, until the end or selected amount of rows. The values of the different columns should be separated by commas Column A values could be 9 characters (5 numerics with 4 decimals - 12345.6789) Column B values could be 10 characters (6 numerics with 4 decimals - 123456.7890) Column C values could be 10 characters (6 numerics with 4 decimals - 123456.7890) The text file therefor would look as follows: This file was exported from Excel. Company ABC On this continent 27/Sep/2007 123.4567, 23.7654, 123.4567 1234.5678, 123.4567, 987.6543 12345.6789, 6.0000, 2.9876 I tried to adapt jindon's code but no luck. I am currently doing it the long way. ='[Workbook1.xls]MySheet'!$A12&", "&'[Workbook1.xls]MySheet'!$B12&", "&'[Workbook1.xls]MySheet'!$C12 This works but I really liked what jindon did and wondered if it could be adapted to fit my needs.
View Replies!
View Related
Search Comma Delimited String
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?
View Replies!
View Related
Replace Comma With Dot In TextBox
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 Replies!
View Related
|
TRACKER
