Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










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:
Merge The Content Of 2 Cells Into 1 (with A Comma Separating The Content)
how to combine the content of 2 cells into one cell and have the information separated by a comma.

For example:

CELL 1:
Software 1

CELL 2:
Spreadsheet Software

The desired results is:

CELL 3:
Software 1, Spreadsheet Software

View Replies!   View Related
Transfer Chosen UserForm ListBox Items Into Worksheet Textbox As Comma Separated List
I want to select items in a listbox and transfer those items via command button in a textbox. The listbox is already filled. I have no idea how to realize that.

Attached is the form I created so far. I copied everything together and matched it up for me. It's probably not the best way but it works. I marked the section where I need help in yellow.

View Replies!   View Related
Excluding Selections: Added To The 20 Items And Not To The 3 Items
If i select 3 items from a list 20 items to be show on the cube this is fine and works however if i then add another item to the list i now have 21 items and for some reason it also adds itself to my 3 selected items to become 4 items. Is there anyway I can get it to be added to the 20 items and not to the 3 items..

View Replies!   View Related
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 Alpha And Numeric
I have data in column with combination of alpha-numeric like "Member # 308166 RCI Number 4801-60436". can I split this without using copy and paste?


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 Or Sorting Data
for example:

Column A.

@yahoo.com
@hotmail.com
@live.com
@msn.com
@yahoo.com
@hotmail.com
@live.com
@msn.com

how can i separate all datas by domain without having to cut and paste them manually.

View Replies!   View Related
Detecting And Separating Integer
A1 : Style 998 45702B WRAP DRESS 1 0 0 0 1

Try to accomplish:

I would like to remove the "1" in between "Dress" and "0"

in other word remove integer from string but starting from right only.

B1 = should contain 1

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
Creating A Unique List Of Items In Column A That Have A Corresponding Non-zero Value In Column B, I.e. Excluding All Items Where Sumif ColumnB Would Sum To 0
I know how to use array formulae to create a unique list, i.e.{=INDEX($G$1:$G$760,SMALL(IF(ROW($G$1:$G$760)=MATCH($G$1:$G$760,$G$1:$G$760,0),ROW($G$1:$G$760)),ROW()))}

however this is giving all the unique items from column G and I only want the unique items that have a non-zero value in column H as well. This would be the sumif of all instances that would have to be zero. I've tried to crack it and I've tried to search for solutions but so far no joy.

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 The Parts Of A Mixed Number
Is it possible to separate the parts of a mixed number? I have a cell in which is generated a decimal number. I can convert this to a mixed number. What I need to do from there is extract the whole number and the numerator.

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 Data With Spaces And Putting In Two Different Cells
I need to take data from a cell that has a space "cherry banana", and make it into two cells "cherry" and "banana". I want to duplicate my line on which the data resides, if possible.

What I have is a sheet like this:

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
Separating Categories Located In One Column Then Combining It In A New Table
im currently working on a project that needs to consolidate the data per given date. but the thing is, all of them are located into one column. i was able to separate each category into separate columns but now, i cannot align the data on a per date basis.

View Replies!   View Related
Separating Data Entries Of Different Lengths All In Single Column
a way to separate out data that has been all entered into a single column

code .....

The above information is a sample of the data. The data has been entered into 1 column, column A.

I want to separate the entries in to 1 column each for vehicle ID, full journal description, actual journal, EE code, Report total and equipment description.

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
Position Of Third Comma In A Cell
How to know the position of third comma in the same cell.


View Replies!   View Related
Remove Comma In The First Character
How to remove the first charcter that contains comma, like this :
,001,003,005 and i want the result is --> 001,003,005.

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
Find Text Right And Left Of A Comma
I have the name bloggs, Joe in cell A1. Is there away to flip the name around and get rid of the comma.

i.e. Joe Bloggs


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
Remove Comma From Text To Numeric
I need a simple way to extract the comma in 14,656 imported text so I can do a vlookup against 14656.


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
Copyright © 2005-08 www.BigResource.com, All rights reserved