# Split Cell Data Into Two Columns

Sep 1, 2009

I am trying to split data from a cell into two columns. I have tried the LEFT and RIGHT functions but as the data held is not always the same number of characters this does not work for the LEFT function. The only constant thing is that the last character will always be in the second column. An example of data would be:

9065-10A
9065-9B
9065-11C

and I need to be split into 2 columns as follows:

Column 1 Column2
9065-10 A
9065-9 B
9065-11 C

## Trim Special Characters In Cell To Be To Split Data Into Columns?

Apr 9, 2014

This formula I want to apply it in another workbook. It split in different columns the content of a cell.

The formula is below:

[Code] .....

In cell A2 I have the following data:
|516582-001-99|414816-001-99|414816-003-99|516582-001-99|

If I apply the formula above in cells B2 to E2 it returns a blank cells. But if I delete the first "|" sign in the left side manually the formula works perfectly by splitting the cell into columns from B2 to E2. The issue here is that I have more than 300,000 records. Just imagine the amount of time invested in just deleting the first "|" at the left side.

I need a variation of the formula above that in first place delete the first "|" at the left side and after that continue with the proper work of the formula.

## Split Data To Many Columns?

Dec 1, 2013

1 1100200157326 7000000000011126 Mr .AA BB CC 30,548.68 16 1,759.00 3,523.75

1
1100200157326
700000000001
1126
Mr. AA BB CC
30,548.68
16
1,759.00
3,523.75

2 1100200313905 700000000002 1127 Miss AA BB 48,329.53 9 1,969.00 3,944.75

2
1100200313905
700000000002
1127
Miss AA BB
48,329.53
9
1,969.00
3,944.75

3 1100200568628 700000000003 1127 Mrs. AA 24,990.00 5 1,825.56 3,652.84

3
1100200568628
700000000003
1127
Mrs. AA
24,990.00
5
1,825.56
3,652.84

I have data on column A and I want split data to many columns. This is Example.

## Split Data In A Cell And Get The Value For The Split Data From A Different Table

Jul 31, 2006

Here's my problem. I have a cell where there are many data strings seperated by ",". Each data string has a seperate value of its own like for e.g:
A2: aa,ab,ac

String Value
aa 1
ab 1
ac 3

What I want it accomplish is that, split the A2 cell into the different data string entities seperated by ",", then get the corresponding value of each of the data string entity, and to take the average of all the values of the different data string entities.

## Split Data In One Column Into Two Columns

Mar 12, 2009

For each cell in column A, I am trying to split the data between two new cells. The data in each cell is separated by a |. All information to the left of | should go in column B. All information to the right of | should go in column C.

Column A
Aa3 |AAA
A3 |AA-
A2 |A
A3 |BBB+
Aa3 |AA
Aa2 |AA+

Desired Results
Column B.Column C
Aa3AAA
A3..AA-
A2..A
A3..BBB+
Aa3AA
Aa2AA+

## Split Data Into Adjacent Columns

Nov 21, 2009

I am looking for two formulas (col. B+C) in order to split every second value from Col. A into two adjacent columns. Empty cells, in col. A, should be ignored. The requested result is shown in green and red.

## Split Data Into 2 Cells, Text To Columns

Feb 26, 2009

i have cells with city and state in them and i'm trying to separate the one column into two columns. the problem is, i'm trying to keep the city name in one column and the state in the other. some of my cells have two word cities like new albany, or upper arlington. the text to columns feature is separating those cells into 3 columns not 2. is there a way to do this?

example:
worthington, oh
upper arlington, oh

text to columns splitting upper arlington into 3 different cells because the only delimiter in the cell is a space. i need to keep upper arlington in one cell and oh in another.

## Split Data In One Column Into Multiple Columns

Jan 26, 2012

I have an address in one cell in excel. Street/City/State/Zip are all separated by two spaces like this: 123 Anywhere St Chicago IL 60607 United States

I want to break them out into separate columns. The "Text to Columns" won't work because there are no commas or dashes separating the data.

## Split String Of Data Into Columns And Rows

Feb 22, 2010

I am looking for a way to split a large string (400 numeric value's, split by comma's) into a 20x20 field.

If possible, the field should be 600x400 pixels (30x20 squares), but this is just extra
Is there a way to do this in Excel (or any other program)?

## Categorizing Data Based On Color Then Split Into Columns?

May 15, 2013

I have one column of data which I need to split into two. Those values that are bold need to migrate to column B. Have some difficulty in phrasing the question properly.

Original:
A
10
0
0
2
4

Desired output:
A B
0 10
2 0
0 0
2 0
0 4

* note a few formatting issues here:
1. instead of bold and not bold, they are in green or in purple
2. 0 stands for an empty cell in my data set (I tried to fill the empty cells with zeros, but they somehow inherit the colors, i.e. some zeros are green and some zeros are purple; guess that wouldn't be a problem since the desired output they'll all be zeros anyway?)

## Split Data Based On 3 Columns Into Separate Worksheets?

May 22, 2014

I need to split the data into worksheets (see attached) by the Advertiser column and then by the deal year and deal code columns. I need each worksheet to be named per advertiser and deal year_deal code. I took off and replaced the data since it is sensitive information.

creating a macro or implicating one into the workbook to run for future reporting.

## Split Cell Into Two And Move To Another Row And Columns

Dec 3, 2009

I have a report that I need to reformat where part of the information is moved from rows to columns.

The report is broken up into "sections" as follows: Each section is a series of multiple rows and is broken down as follows:

ROW 1: Contains data (in a single cell) about a Sales rep, which includes (1) rep number & (2) rep name

NEXT ROW(s): Contains data information about an invoice(s), which includes date, invoice number, client name, trans ID, etc. The invoice data can be one row up to as many as 500 rows
LAST ROW: Contains the Rep Subtotal

I need to spit out a report that contains the invoice data only (the middle part of the section). I don't want "ROW 1" or "LAST ROW" of each section in the output. For each invoice row, I need to include the rep number and the rep name for each invoice. As noted, the rep number and name is always listed in the row preceding the invoice data. The format is always a 6-digit code followed by the name. So I need to split the data into two pieces.

## How To Split The Content Of A Cell Into Separate Columns

Aug 12, 2014

In A1 I show the following content: 5,12,27,36,124

How can I make this split to show the following layout:

A1: 5,12,27,36,124 B1:5 C1:12 D1:27 E1:36 F1:124

My worksheet has thousands of lines so hoping for a quick and easy formula that I can pull down and copy for the entire sheet?

## Split Concatenated Delimited Cell Values Into Separate Columns

Jun 20, 2014

Im trying to come up with an automated way of splitting concatenated information but putting each concatenated value in a seperate row.( i have columns of data that need to stay with each concatenated value) Eg

Concatenated comma delimited data

Cust Name Cust Identifier Locations
L Kim 543545 California,Chicago,Florida
S David 31434 Maryland,Vermont,Maine

i need to to get a result that looks like the below example im currently just doing text to column filterting and copying all the columns over and stacking them on top of each other. i need to find a way to automate this process but i cant seem to think of one

Customer name Customer Identifier Location
L Kim 543545 California
L Kim 543545 Chicago
L Kim 543545 Florida
S David 31434 Maryland
S David 31434 Vermont
S David 31434 Maine
D Bryant 572575 Texas
D Bryant 572575 Oaklahoma

## Split Data From One Cell...

Mar 9, 2009

I'm working on a datasheet that needs some tweaks...

In column A, I have these data
CA 902-20
GA 100-10
UP 200-20
WC 100-20
UP10-299
Best99-000
City #123-89

how can I tidy the data into column B so that they have it will have a standard format of a single letter followed by (no space) 5 digits.
ex: C90220
G10010
U20020
W10020
U10299
B99000
C12389

I am thinking using Left and Right formula, does that sound right?

## Split Data Within A Cell?

May 29, 2009

My data within a cell has 3 values separated by commas i.e. abc, def, ghi. I want to separate these values into their own cells. Is there a formula or command that can do this? of course I can go into each cell and cut and paste the values onto another cell, but I have thousands of cells that need to be reformatted.

## Text To Columns: Split Data In Seperate Seperate Coloumn

Jul 23, 2009

Attached file where i m not able to split data in seperate seperate coloumn

## Text To Columns/time Formatting Split Them Into Columns

Jan 5, 2010

I've got some time values in an Excel Sheet in the format hh:mm:ss. I need to split them into columns (including the colon) like below:

hh: | mm: | ss

I can do this manually using text to columns but when I use text to columns in my macro, it automatically changes the time format to h:mm:ss PM

## Split Data From 1 Cell Into 2 Cells

Jun 17, 2009

I have a list of first names and surnames, the first name and surname are in one cell together but seperated by a commer.

For example in A1 would be Steve, Jones

What I want to do is have this split into two new cells. So in A2 I would have his first name and in A3 his surname. Formula to do this is what I cant get to work.

## Split Cell Data Into Rows?

Feb 5, 2014

I have data in column A and Column E & F shows the required output.

## Split One Cell Data Into Different Cells

Jul 10, 2009

macro of the attached sheet.

Actaully i want a macro which are splits one cell data into deiffernt cells or rows.

## Split Data In One Cell Into Below Rows?

Sep 23, 2012

I have data in two Columns one Columns contains data with different line in each cell

like

MR X
MR Y
MR Z

and in the other column with single line like

Success
Failed

see the below image for clear view.

In this case, I want to split the data in to each line in to each row.

Like MR X Failed
Mr Y Failed
Mr Z Failed and so on..

## Split One Cell Data To Different Cells

Aug 11, 2013

Ihave data in column "B" like bellow & i want split that one cell data to different cells base on space. i mean after each space data should add to next cell ( from column "C" data can split). (formula or macro)

41981 (C-2313) TEX-120 3000 M - Poly Poly Core - Silver Falcon

41981 (C-2313) TEX-60 3000 M - Poly Poly Core - Silver Falcon

42684 (C-3899) TEX-120 3000 M - Poly Poly Core - Silver Falcon

## Split A Cell Of Data Into Multiple Rows?

Mar 22, 2013

I have some data from web-site, when I copy paste these data to Excel is only in 1 cell and i would like to split it into multiple rows by comma.

## Split Cell Data In Multiple Rows

Jul 18, 2006

I was just not able to figure out how to write a macro for splitting the cell data into multiple rows with the other column values being duplicated. Just elaborating my problem, its like i have a column in which i have multiple values separted by a comma, So i need to divide all these values on basis of a comma and then copy this value to a new row below, along with the previous column values same for this new row. in my file cost center column is to be worked upon. Can this be done by a macro ?

## Separate/Split Data In One Cell To X Cells

Jun 3, 2008

How do I split data in one cell into three cells?

example:

From

(A1)100 CARIBBEAN VILLAGE DR

To

(B1)100
(C1)CARIBBEAN VILLAGE
(D1)DR

Not all the data is the same, some have more words than others.

## Split Data In Single Cell To Other Cells

Aug 7, 2008

What I got going on is a program I use for reporting is shooting out information.. while most of the information it sends to an excel workbook is sectioned by cell, there is a good chunk of information that's dumped into 1 cell. Is there a macro, or something, I can do to separate the information in this single cell, into separate cells? Auto Merged Post Until 24 Hrs Passes;Here is an example of the report. You'll notice a bunch of information in the 'Description' section. What I want to be able to do is run a macro that will seperate the 'Item Number', 'URL', 'Serial Number', 'Model Number', and 'Details' information into seperate cells.

## Split To Columns

Feb 1, 2009

i have in col A using 2007 these examples .

10. * 7x7 Showmed'cash (7) 3g b
5. * 08x90. Bean Rock (10) 6g b
8. * 31 Thenaia (12) 3f b
11. * Stormy Masika (9) 3g b

I have managed using text to cols separateting info inside the brackets and to the right .
Also the numbers very left .

My problem is there arnt any more things to use in text to cols to separate examples 7x7 Showmed'cash , 08x90. Bean Rock , etc .

## Split Columns Into 1

Apr 2, 2007

Excel 2003

I have one column with a date and on the same row 12 other columns with nos

How can I convert this to 2 columns - first date and 2nd the no

## Split Text Into Columns?

Jun 4, 2014

I am having quite a bit of a challenge here and am not able to code to split the text into columns. The text to columns does not work here unfortunately. Below is my situation. In one column that has the contract details I have the data as follows:

Account Manager Jennifer MacFarlane CONSULTING - GENERAL on 20-JUN-13 Function #:176749
Account Manager Janet Bewers CONSULTING - GENERAL on 25-JUL-13 Function #:176878
Account Manager Janet Bewers HEAT STRESS AWARENESS on 27-JUN-13 Function #:176828
Account Manager Janet Bewers TRACTOR SAFETY AWARENESS on 08-AUG-13 Function #:177383

What do I key in to get Account Manager in one column, the name of the person in another column and the one in caps in another column and the date in one column and the function in another column. I tried using left, right and LEN and something is terribly wrong with my logic