Separate Data At Commas

Nov 12, 2009

I've imported a large document to excel. It contains 118 rows of information. My problem is that all the information is in column A. Every cell contains long sentences with information. The information is divided by a comma-sign (,). What i want to do is to seperate the information by columns instead of a commasign. So for example:

...A....................................................B....C.....D
1 Div,Date,HomeTeam,AwayTeam,FTHG
Becomes:

......A....B ........C.............D..........E
1 Div Date HomeTeam AwayTeam FTHG

Is there any smart way to do this??

View 3 Replies


ADVERTISEMENT

Separate String By Commas

Oct 2, 2009

I have the data like this :
03/07/2009;31267400;78168 in Cell A5

And here i want change that data like this :
Cell A6 = 03/07/2009
Cell B6 = 31267400
Cell C6 = 78168

View 2 Replies View Related

Separate A Column By Commas

Aug 17, 2012

I have data in this formatItem

# Data
A
1, 2, 3, 4, 5

And I am trying to see if there is a way to separate the data column into separate colums anywhere that a comma is present.Need data to look like this.

Item #
Data
Data
Data
Data
Data

A
1
2
3
4
5

View 2 Replies View Related

Formula To Separate Numbers With Commas To Other Columns

Dec 21, 2013

I need the formula to separate the numbers with commas in a cell to other columns as shown in sample.

This data comes from internet and automatically updating certain intervals, so the text to columns option has no use.

View 2 Replies View Related

Separate Names From Dates & Commas In Cell

Mar 26, 2008

I have a file with list of names in the attched file.They are not in the same format coming from the source in my company. I need only the names in the separete column without the dates and the commas. The text is not in the same format. Looking at the data, can someone help me make a formula to separate only the names from the the entry.

the data is huge and it takes lots of hours to clean this data.

I am attachign the excel file for reference.

View 4 Replies View Related

How To Separate Row Data From One Workbook Into Separate Workbooks Based On Cell Data

Apr 23, 2014

I need to separate row data from one workbook into separate workbooks based on cell data. Currently I filter the data, copy and paste it to a new workbook and save it. It's a tedious process and was hoping to find a way to automate it. I have attached a sample file. In this instance, I would like a separate workbook for the filtered data in Column 1 and then all the row data gets copied to new workbook. So all of Pennsylvania data would get copied to new workbook, then all of the Michigan data gets copied to a new workbook. I have also attached an example of the end result that I need.

View 9 Replies View Related

Macro To Transpose Multiple Data In Cell Separated By Commas To Each Data In Column

Jul 15, 2014

I have a table in the format below with about 3500 rows

Column A
Column B

0001
All vehicles, Retirements

0002
All vehicles, Retirements, Addition

0003
All vehicles, Retirements, Addition, Deletion from Y

I would like to change it to the following format:

Column A
Column B

0001
All vehicles

0001
Retirements

0002
All vehicles

0002
Retirements

0002
Addition

0003
All vehicles

0003
Retirements

0003
Addition

0003
Deletion from Y

View 3 Replies View Related

Adding Data From Multiple Columns Into One, Data Seperated By Commas

Mar 27, 2009

Using =A7&","&B7 to add data from two columns into one, separated by a comma. What about multiple columns, still separated by a comma? See Example in attachment.

View 2 Replies View Related

VBA- Remove Commas From CSV Data

Apr 26, 2009

i have data exported as CSV from in house system, the problem is that data is separted by commas therefore some of cells have split, i need VBA to remove the commas and bring my data back into correct format. Below is a sample of what the data looks like, real data is 5000 rows of data

PFOLIO A/C CODE ACCT NAME CUR CODE DESCRIPTN NARRATIVE DATE ENTRY DATE CASH VALUE De --------------------------------------------------------------------------------------------------------------------------------------------ACA001AUDCUST JP MorganAUDINT0005 SALE 39727.08 INTECH BAL GR FD C29-Dec-082-Jan-09-27690.57N

View 9 Replies View Related

Counting With Data Separated By Commas

Feb 19, 2013

I am currently trying to count data in one cell separated by commas. The spreadsheet attached will make things look a lot clearer.

The "CURRENT" table is what I currently have and the "IDEAL" table is what I would like (but not hard-coded). Sheet 3 is where the meaningful data is. So for example, E4 has "CC-12" which is "Open" and "CC-11" which is "Closed". Therefore I would want there to be a "1" in cell F4 and G4 and a "0" in H4.

Formula to put in F4:H5?

View 2 Replies View Related

Exporting Data In A Column Out Of Excel WITH Commas

Dec 11, 2009

I have an excel file with a single column that looks like this:

A
HYU
NVT
FYR
NUH
GFR
TRF
GXA
AKL

My question is how do i export the data out of excel so that I can have a text file that reads like this:

A,HYU,NVT,FYR,NUH,GFR,TRF,GXA,AKL

View 2 Replies View Related

Export Data To Text: Space After Commas

Nov 10, 2006

I would like to export some data from excel to a text file. An example of text in excel is as below. which each number is in a separate cell.
1
12
123
1234

I tried to exported it as csv format and open it with text editor. it become
1,,,
1,2,,
1,2,3,
1,2,3,4
,,,,
,,,,
,,,,

however I would like the text file looks like this with a space between each comma.
1
1, 2
1, 2, 3
1, 2, 3, 4

What the best way to do it?

View 2 Replies View Related

Remove Blank Cells From Row - Add Commas Between Data In New Cell

Jan 10, 2014

I have a column of contacts. Each contact has data spread out over many rows, most of which are blank. I would like all of the data spread out through the rows to be in one cell, delineated by commas (so I can import into another program).

Concatenating the cells works except that I end up with 20 commas for two strings of info.

Just so you understand I may have this:
Mary red blue yellow purple
Rob blue purple
Trey yellow

and I want it to look like this

Mary red, blue, yellow, purple
Rob blue, purple
Trey yellow

How should I go about this?

View 14 Replies View Related

Copying Cells Data Loses Commas And Changes Formatting

Feb 12, 2013

i am trying to import an excel document and then copy over some columns to another worksheet from the imported worksheet, using a macro. Everything imports and copies over fine except for one column that has cells that comprise of a list of number separated by commas. When these cells get copied over some of the values retain the commas but some of them (specially when there are a lot values separated by comma) gets changed to this format

From: 1,229,124,012,441,230
To: 1.22912E+15

From: 1044,1048,1052,1053
To: 1.0441E+15

From: 1279,1282,1286,1295,1299
To: 1.27913E+19

From:926,929,938
To: 926929938

I am trying to figure out whats happening here! Is there a max number of letters this column can take and if it exceeds that value, excel converts them to this format? Or is the formatting gets changed to something else while copying and pasting?

View 3 Replies View Related

Formula To Separate Specific Data From Data List In Sheet1 Into Sheet2 / Sheet3

Jun 30, 2014

I have attached a sheet that has part of a data list, sheet called (Full Data) what i am trying to do is seperate the data into event locations into individual sheets.

The data ref will be column F which is the different event locations.

I thought the best way to try and do this was to create an if/ match formula using the sheet tab names as the if match, with the event locations in column F.

I have added two sheet tabs so you can see that i require the data for (Ain) to be put into the Ain sheet.

The data list in Full Data sheet will be continuously updated so i will need the range to be around 10,000 entries.

View 5 Replies View Related

Shade A Cell Red If Two Separate Conditions Apply (in Separate Cells)

Dec 3, 2013

I'm trying to use a formula in conditional formatting to highlight a cell red if the cell contains a 0 but the date shown in another cell has passed. I want to copy the formatting throughout a column but I don't want the cell to highlight if there is no date in the other cell concerned.

View 11 Replies View Related

Separate Letters And Numbers In A String Into Separate Columns

Nov 20, 2012

I receive an extraction from AutoCAD that lists the electrical devices in a drawing. I don't have any problems extracting the letters. I have a problem extracting the device number and the device number extension.

The device label extraction is similar to this:
DCM1005-1
DCM1005-10
DCM1005A
MTR1005-1
MTR1005-10
MTR1005A

I want to create 3 columns from the device label: (I separated the column with commas)
A1, B1, C1, D1
DCM1005-1, DCM, 1005, 1
DCM1005-10, DCM, 1005, 10
DCM1005A, DCM, 1005, A
MTR1005-1, MTR, 1005, 1
MTR1005-10, MTR, 1005, 10
MTR1005A, MTR, 1005, A

View 5 Replies View Related

Separate Numeric / Text Combination Into Two Separate Columns

Oct 9, 2013

How can I separate the following numeric/text combination into two (2) separate columns in Excel?

302ALTO
406AMZN
451AMRC
404AMAD
605ANCC
405ADRC

The result would be:

302 ALTO
406 AMZN
451 AMRC
404 AMAD
605 ANCC
405 ADRC

View 6 Replies View Related

How To Separate Text From Numbers Into Two Separate Cells

Feb 13, 2014

I'm trying to separate text from numbers into two separate cells...

Essentially, I would like the users to copy and paste data into Column A, as seen below. Then, hopefully by formula separate the text characters into Column B and the numbers into Column C.

Input: Output 1: Output 2:

Col A Col B Col C
Wells 123 Wells 123
Wells 1234 Wells 1234
Wells Fargo 123 Wells Fargo 123
Wells Fargo 1234 Wells Fargo 1234
Wells Fargo Inc 123 Wells Fargo Inc 123
Wells Fargo Inc 1234 Wells Fargo Inc 1234

Ideally, I would like to do this with a formula...

View 6 Replies View Related

Separate Workbook By Value In Column Into Separate Workbooks

Feb 25, 2013

I have 10 very large workbooks that are all setup in the same format. In column Z is a numerical value from 1 to 83. I have been trying to filter the sheet and then copy one at a time from 1 to 83 but that takes a LONG time especially when there is 10 workbooks to do.

Is there anyway I can run a function or macro or something that would just automatically look down the column Z and put each row into a it's own workbooks?

I have attached a sample of what the workbooks look like right now.

Sample123.xlsx‎

View 4 Replies View Related

Creating Separate Reports In Separate Spreadsheets

Nov 21, 2006

I have a giant data sheet. I have to cherry pick certain data out of the list and send it to certain associates, in a new spreadsheet.

I want to do this automatically.

I can create the macro to select the data I want, and copy it over (values only), but I cannot find out how to do it in a new sheet with a new name that automatically gets generated with a unique filename (probably using the date).

Essentially, I want to copy my data spreadsheet (post-filter) into a new spreadsheet titled X mm-dd (where X is whatever I pick). I then want to delete several rows from it.

Ideally, the macro would do this (I will put a * next to things I can handle the code for, to save work):Filter Data *
Copy Data *
Create a New Spreadsheet named (Person's Name) mm-dd.xls
Paste Data (values, formats, column widths) to the new spreadsheet *
Delete Several Columns from the new spreadsheet *
Rename the sheet it's been pasted to
Re-filter data (new filter) *
Copy Data *
Paste Data (values, formats, column widths) to the same spreadsheet, only Sheet 2 *
Set the header of the spreadsheet with (Person's Name) and the date
Save the new spreadsheet

There will be as few as 1 sheet (in which case I would only want one worksheet in the new excel file), or as many as 4 sheets. I don't need a fancy way to do this, as it will only be done once a week, and I can just make the various cases required to do it even if it isn't pretty, but I can't figure out how to manipulate other files that haven't been created yet...

View 9 Replies View Related

How To Separate CSV Data Into Different Columns

May 22, 2013

how to separate .csv data into different columns in excel?

e.g this .csv data

2012.09.07,07:00,125.78800,126.12500,125.76800,126.02300,6664
2012.09.07,08:00,126.02100,126.19800,125.93900,126.05000,4707
2012.09.07,09:00,126.05100,126.11300,125.96700,125.99800,4178
2012.09.07,10:00,126.00000,126.02900,125.71700,125.77800,6389
2012.09.07,11:00,125.78300,125.91400,125.62800,125.66400,5388
2012.09.07,12:00,125.66300,125.86900,125.04100,125.12300,10447

[Code]....

View 11 Replies View Related

Separate Data Into Columns?

Jul 22, 2014

I have some data which i want to split, i have tried " Text to columns "on excel but couldnt find a way it works.In one cell my data is exactly like this:

Name of product
Code of product

Loc.: here is the location
expire date:

I would like each one of these itens on one column, like

A b c d
Name of product code of product Loc.:here is the location expire date:

View 11 Replies View Related

How To Separate Cell Data

Aug 2, 2012

C5=ACDefg-0087HzY
In D5, I want 1st 4 letters (EXACT)
In E5, last 10 letters (EXACT)

How to accomplish?

Ans
D5=ACDe
E5=gf-0087Hzy

View 2 Replies View Related

Uneven Data To Separate

Dec 13, 2012

I want to separate data in cols A,B,C to match my results in cols D,E,F,G .

Have tried using left , mid , right functions but data is not evenly matched .

The most important parts I need is the results in cols D , E and G

Sheet1  ABCDEFG1ABCResult AResult BResult CResult D23SurvivedJonathan Riddell3SurvivedJonathan Riddell 37RacquetorCraig Grylls7RacquetorCraig Grylls 42KodemaOpie Bosson2KodemaOpie Bosson 56-Clutha Lad-Shane Dye(2.5L),  6Clutha LadShane Dye2.568-Viceroy-Johnathon Parkes(3L),  8ViceroyJohnathon Parkes3711-Omana-Rory Hutchings (a)(5L),  11OmanaRory Hutchings589-Sunbeam-Matthew Cameron(6L),  9SunbeamMatthew Cameron6912-Zulou-Kelly Myers(7.8L),  12ZulouKelly Myers7.8105-The Sheriff-Hayden Tinsley(12.6L),  5The SheriffHayden Tinsley12.61110-Inazuma-Bridget Grylls (a3)(12.9L).  10InazumaBridget Grylls 12.9

View 6 Replies View Related

How To Separate Data To Different Columns

Jan 28, 2014

I have a huge data and want to know the best VBA or formulas to separate to consecutive columns.

Example :

BERNATTE QUIGLEY 10 WESTBOURNE ROAD STOCKTON HTH WA4 6SE
J QUINN 100 CHRISTOPHER TAYLOR COURT FLAT 18 B30 1ER
JOAN QUINN LITTLE COURT CT6 6PT
PAMELA QUINN ROAIN BANKS COTTAGES CT4 5PU

[Code] ..........

View 3 Replies View Related

Lookup Data From 2 Separate Columns

Mar 28, 2014

I am working on a workbook, it's not my workbook so I can't change how it's laid out.

I am trying to figure out a way that excel will check 2 different columns for a code that is manually entered, then enter the codes description in another cell.

It's basically a deposit and expense ledger.

One column has expense codes and names, the other deposit codes and names.

I want to be able to enter the code letter into a cell in the ledger and excel will enter the code description into another cell.

View 11 Replies View Related

Separate Sheet Data Collection

Oct 31, 2009

I have a separate sheet(Coverage.jpg) that records some data which at this stage has to be entered manually.

The data comes from another sheet(officers.jpg), each row is 1 flight and the days are usually separated by a blank or grayed row.

I was wondering is there a formula that will collect the data automatically.

As you will see on Coverage.jpg it is broken down into Number of flights(per day), how many flights were covered by 2 or more officers and how many covered by 1 officer.. and then the graph generates off the data.

Is there a formula or something that will enter the per day data?

I have just added some false data to show you how some things get recorded.

View 14 Replies View Related

Separate Data From One Column To Several Columns

Jan 3, 2014

I'd like to copy information from a PDF into a worksheet. The information looks like this:

1 PAOZZ 5310-00-918-0482 80205 NAS1291-7 NUT.............................................................................. 1
2 PAOZZ 5305-00-995-2125 80205 NAS603-7P SCREW.......................................................................... 2
3 PAOZZ 5305-00-866-0937 80205 NAS603-8P SCREW.......................................................................... 14
4 PAOZZ 5306-01-106-8238 80205 NAS6603-3 BOLT ............................................................................ 8

When I paste it into excel it all gets entered into one column, I'd like to spread it out over 7 columns. Is there an efficient way to do that?

View 2 Replies View Related

VBA To Add Textbox Data To Separate Rows

Jul 16, 2014

I have a long textBox with multiLine = true and enterKeyBehavior = true. I need for each new line in the textBox to go to a separate row in the worksheet. Right now the data is copied to the next row in the worksheet, but all of the data in the textBox is entered into one cell. Is there a way for the lines to go into separate cells?

Example of what i am trying to accomplish:

TextBox:
|------------------|
|blabla in line 1 |
|blabla in line 2 |
|blabla in line 3 |
|etc................ |
|etc................ |
|_____________|

then the VBA code would separate the lines into separate rows:

Worksheet:

Row 1: blabla in line 1
_______________________
Row 2: blabla in line 2
_______________________
Row 3: blabla in line 3
_______________________
Row 4: etc..
_______________________
Row 5: etc..

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved