Split Single Cell Into 2 Column X 3 Row Range

Mar 25, 2008

I'm trying to separate a cell containing:

TSB w/6.5% NaCl - NS 29002TSB w/6.5% NaCl - NS 29003TSB w/6.5% NaCl - NS 29004

into a 2x3 table, in another sheet, like this:

TSB w/ 6.5% NaCl NS 29002
TSB w/ 6.5% NaCl NS 29003
TSB w/ 6.5% NaCl NS 29004

TSB..... in left column and NS...... in right column. This example includes 3 items as shown above but the single cell could contain more or less than 3 items

View 7 Replies


ADVERTISEMENT

Split Single Column Into Multiple Columns And Rows

Aug 20, 2008

I have 300 rows worth of data that looks similar to this, all organized in one column:

John Q. Smith
Programmer
2111 NW 13th St
Anywhereville, USA, 55555
(555) 555-5555
Joe P. Snider
Organizer
5645 NW 45th St
Anywhereville, USA, 55555
(555) 555-5555
Patty Williams
Accountant
6454 NW 34th St
Anywhereville, USA, 55555
(555) 555-5555

As you can see it is consistent with the name, position, address line 1, address line 2, Phone number, for every single entry. All my names are already alphabetized so I don't have to worry about it. What I want to do is have the information for each entry translated into 5 separate columns so it looks like this.

John Q. Smith Programmer 2111 NW 13th St Anywhereville, USA, 55555 (555) 555-5555
Joe P. Small Organizer 5645 NW 45th St Anywhereville, USA, 55555 (555) 555-5555
Patty Williams Accountant 6454 NW 34th St Anywhereville, USA, 55555 (555) 555-5555

Supposing I started the sheet in the top left corner at A1, I was just going to have cell B1=A1 then Cell B2=A6 then just autofill down column B but it doesn't work. I have seen some people do something similar to what I want with VBA but I am convinced there has to be something simple in with an excel formula seeing as my information is already so organized and consistent.

View 4 Replies View Related

Split Phone Numbers Into Single Column & Remove Duplicates

May 4, 2008

I have to take a range of phone numbers that I have to port from another carrier and manually type them out or copy into another speadsheet, removing duplicate numbers. For example, I have the following numbers:

55532821055553282108
78132821235553282123
55532821625553282163

The results on the next spreadsheet need to be:

5553282105
5553282106
5553282107
5553282108
7813282123
5553282162
5553282163

I am looking for a way to automate this process.

View 8 Replies View Related

Split Single Cell Into 2 Or More Cells

Apr 24, 2012

I would like to know if you can split 1 single cell into multiple cells in 1 column?

View 11 Replies View Related

Split Numbers In Single Cell?

Aug 23, 2012

I have a string of numbers like this:

378 398 308 978 397 328 078 307 908 278

And this code works fine except for I get a Type 13 Mismatch error on this line of code:

Code:
x = Application.Transpose(Split(my_cell.Value, " "))

Here is the entire sub:

Code:
Public Sub split_down()
Dim x As Variant
Dim my_range As Range

[Code]....

How can I adjust this for several lines of numbers like this?

378 398 308 978 397 328 078 307 908 278
390 327 928 678 392 907 208 320 927 178
608 394 207 578 391 920 108 607 395 324
508 321 168 107 325 924 568 507 341 921
167 345 925 518 567 351 941 160 945 517
560 951 241 245 510 251 516 451 388 338
788 988 337 088 778 339 377 288 330 998

And what is going on with this error. The first number string above should be placed in cell A1 when trying this code.

View 4 Replies View Related

Way To Split A Single Cell In To Two Cells?

Jan 20, 2007

Is there a way to split a single cell in to two cells?

View 9 Replies View Related

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.

View 2 Replies View Related

How To Identify Last Cell Of Single Column Range

Mar 30, 2013

How can I identify the last cell of a single column range . . . i.e. the last cell address of range C1..C15 is C15. The range will have a name of "reg".

View 9 Replies View Related

Split Multiple Dates Stored In Single Cell

Jun 14, 2008

how to read mutiple values from one cell. For example i want to have one cell for site visits, and there may be more than one. So i want to let the user enter more than one date seperated by a ",". Is there anyway of going about this?

View 5 Replies View Related

Transpose Column Range Into Single Cell With Delimiter

May 14, 2008

I want to add a Punctation mark (comma), like this: ,
and also want to add punctation mark (colon), like this: :

In this moment I have below macro:

Public Sub CombineCells
Dim Combined As String
Combined = ""

For Each Cell In Selection
Combined = Combined & Cell.Value & ":"
Next Cell

Selection.Cells(1, 4).Value = Combined
End Sub

the effect shoud be like this:

before:
--A
1-C
2-D
3-E
4-F
Etc.

after transposed:
--D
1-C:D,E:F Etc.

View 3 Replies View Related

Split Single Text Cell Into Multiple Rows Using Comma Delimiter?

Jul 3, 2014

I want to split the contents of a single cell(ALT Enter as delimiter) into multiple cells and retain the values in column B.

Ex:

A1: apple
banana
car
house

A2: yellow

B2: building
x
y

B3: O

Output:

sheet 2:

A1: apple B1: yellow
A2: banana B2: yellow
A3: car B3: yellow
A4: house B4: yellow
A5: building B5: O
A6: x B6: O
A7 B7: O

View 9 Replies View Related

Split Single Text Cell Into Multiple Rows, Using A Comma Delimiter

Dec 1, 2008

I have a string of text in one cell on Sheet 1 (ie., A1, Sheet 1), here is a excerpt:

A-dec International Inc., A. Bellotti, A. DEPPELER S.A., etc ...

What I need to do is split the cell into separate rows, using the comma as a delimiter. I will be reading the cell from another sheet and need a formula that will provide me with

A1: A-dec International Inc.
A2: A. Bellotti
A3: A. DEPPELER S.A.

View 9 Replies View Related

Split Range Of Numbers From One Cell Into Column Of Multiple Cells All With Individual Numbers

Mar 5, 2012

I have a mass of data which look something like this:

table removed

and I require the ranges of reference numbers to be listed in a column one above the other, which requires inserting new rows. I also need the date & description columns copied down into the newly inserted rows.

So basically for example I would want the top row to now read:

table removed

and then apply the same procedure to the other ranges below this.

View 4 Replies View Related

Split Address Which Is In Single Column Which Contains First / Last Name And Address Without Any Comma?

Apr 24, 2013

I have a list for addresses in excel in single column as shown below - aanandhi narayanan 3430 chemin de riviere sanjose,CA95148

DOMINIC ABANO 3838 GLENGROVE WAY SAN JOSE, CA 95121

abdi abdi 5390 monterey rd #6 sanjose,CA95111

Sheribel Abinsay 3212 Gateland CT San Jose, Ca 95148

I need the result to be in a way like -

3430 Chemin de riviere
San Jose
CA
95148

3838 Glengrove way
San Jose
CA
95121

5390 monterey rd#6
San Jose
CA
95111

3212 Gateland CT
San Jose
CA
95148

I have around 12000 records with the same format.

View 9 Replies View Related

Multiple Row, Single Column Cell Blocks Into Single Row, Multiple Column Format

Mar 21, 2008

I have a text file containing internet explorer browser history. The file has data in the following format (in Excel all data is in 1 column): ...

View 9 Replies View Related

Copy Multi-Column/Row Range Into Single Column

Apr 8, 2008

I have a contiguous multi column, multi row numeric range that I want to copy (and sort in ascending order) into a single column on another sheet in the same workbook. What VBA code could achieve this, or alternatively can this be achieve via formulas?

View 3 Replies View Related

Split Single Workbook Into Several Files

Dec 5, 2008

I have a file which contains a list of all employees and the extra vacation time they have purchased by manager. I need to create a separate workbook for each manager saved as whatever is in the Manager Field, in a folder that I specify. I would like to have a VBA macro that does this.

No cells contain formulas, but I do need the formatting to be the same.

I have attached a small sample of the file that needs to be split. Since there are three managers in this file there should be three workbooks created.

View 9 Replies View Related

Access Single Column In A Range?

Oct 25, 2013

I have a named range that is 5 columns wide by 100 rows. On another part of the spreadsheet I want to have a column point to just one of the columns of that range. How can I reference that column using a cell formula? I have a lot of these all over my spreadsheet, so i don't want to have to assign each individual column its own name.

View 2 Replies View Related

Range Of Data To Single Column

Apr 11, 2007

what's the quickest way to convert an array (range) of data to a single column?

I have varying numbers of rows with varying cells of data in each row. I need to quickly compile (transcribe?) all the data into a single column.

View 9 Replies View Related

Return Nth Value From Single Column Range

Apr 29, 2008

Assume A1:A10 filled with the characters A to J.

I'm looking for a way to return the letter "C" when 3 serves as the index_num BUT I do not want to present all 10 characters in the formula.

I would like to use the range A1:A10 =Choose(3,A1:A10) or, if applicable, to name the range as DATA and then use: =Choose(3,DATA)

I tried all I know, including INDIRECT - but found no solution [is there any ?]

As I'm familiar with all workarounds - I will appreciate replies that use CHOOSE.

View 9 Replies View Related

Split Cell W/o Text-2-Column

Apr 7, 2008

I have a cell that has this in it:

"5+5"

That's how the data arrives to me. I would like to have another cell display the result (i.e. 10) so I won't need to calculate everything by hand. How can I do this with a formula? I don't want to use text-to-columns, and I'd prefer not to use VBA.

View 13 Replies View Related

Add Single Column To Defined Print Range

Dec 6, 2013

Currently i have a macro that prints range column A to O but i need to add column AD on the end of the O so in effect Ii would get A:O & AD. Current macro is below:

Sub PRINT_CALL_OFFS()
'
' PRINT_CALL_OFFS Macro

Sheets("DFS Result").Range("A:O").PrintOut
Sheets("CAR Result").Range("A:O").PrintOut
Sheets("ACU BOS(Z1)").Range("A:O").PrintOut
Sheets("ACU LON(Z2)").Range("A:O").PrintOut
Sheets("ACU MIDS(Z3)").Range("A:O").PrintOut
Sheets("ACU SW(Z4)").Range("A:O").PrintOut
Sheets("ACU WALES(Z5)").Range("A:O").PrintOut
Sheets("ACU SOTON EXP").Range("A:O").PrintOut

End Sub

I have tried adding & AD as follows:

Sheets("DFS Result").Range("A:O" & AD).PrintOut

However this doesn't work.

View 5 Replies View Related

Delete Certain Range Of Rows In Single Column?

Jun 10, 2014

I need to delete a certain range of Rows in a single column

View 3 Replies View Related

Multi Columned Range To Single Column

Mar 7, 2008

I have numbers in a range over an indeterminate number of rows and 6 columns. I want to create a single column of the numbers with no blank cells in between. It's to check Visa receipts from different depts.

View 2 Replies View Related

Excel 2010 :: VBA Macro To Split Text In Every Cell Of The Same Column?

Apr 16, 2014

Excel VBA 2010.

I have this problem:
e.g Cells ""
C2 - 128.50 g
C3 - 211.01 g
C4 - 198.50 g
C5 - 179.34 g

I need to split the text into
Cell "C2" = 128.50 and Cell "D2" = g
Cell "C3" = 211.01 and Cell "D2" = g
and so forth

I wanted to avoid using delimited method with space and induce an automated method to format every cell of Column "C" one by one.

View 6 Replies View Related

Split & Copy Groups/Segments Based On Cell Value In Column

May 6, 2008

This is my first post but I have been using Ozgrid for awhile now. I am farily good with excel formulas but have just started with macros so bear with me if i dont understand what you mean at first.

I am looking for a way to copy rows our of sheet2 in the attached sheet based on the value in the segments column in sheet 2. The rows need to be paste into sheet3 (already has heading set up). The segments value is the number of times i need each row copied into the next sheet. The purpose of this is to split random length samples into 10cm incriments for study. For example, a 1.5m sample is taken so there should be 15 segments of 10cm each copied into sheet3.

Also, if possible, it would be nice for it to display the actual length of the segment after copied into sheet3 for cases where the length was not evenly divisible by 10. I have found several examples of row copying macros, but none that will copy a conditional number of hte same row based on a cell value. In the original data there are close to 4000 rows, but the number of rows will vary depending on the data source.

Another thought I had was if the total number of available rows is going to be exceded would it be possible to have the rows pasted into different sheets based on the rock type listed in the column?

View 9 Replies View Related

Return Range Of Numerical Values In Single Column Based On Frequency Percentage

Oct 25, 2005

I have a single column of numerical values that may repeat many times within
the column.

I require a flexible Formula:
Use an Input Cell for the specified and changeable Percentage(s) %.
Column may be filtered – so only take into account Visible Filtered Cells.

The Formula will calculate and Return the numerical range of values that fall
between the specified percentage % (using the Input Cell) e.g.; 70%.

The Formula should Return two numeric values: a Start Value and an End Value
– NOT necessarily the minimum and maximum per se BUT the MIN and MAX of the
values that appear 70% of the time in the column; therefore, taking into
account Repeat / Duplicate values.

The calculated Results: the two numeric values will be returned to separate
cells on a new Sheet.

View 9 Replies View Related

Excel 2010 :: Return Range Of Cell Concatenated Based On Single Cell Value

Jul 2, 2012

I have a range of cells each containing a name. Based on a number that has to be entered manually I want excel to return the names concatenated in one cell. So for example:

Number of variable entered: 5

q9001
q9002
q9003
q9004
q9005
q9006
etc.

Should give me: "q9001 q9002 q9003 q9004 q9005"

I have been trying to work with formulas using IF and CONCAT functions. But so far I haven't figured out how to have excel return me the correct amount of variables for each separate number that can be entered seeing the number of variables entered can vary from 1 up to 50.

(Using Excel 2010)

View 7 Replies View Related

Pull Keywords From Cell Range And Combine In Single Cell

Aug 1, 2014

I am in need of a way to pull keywords listed in B2:B10 from text in A2:A10 and then those pulled keywords combined in cell C11.See attached document. So I have colors for keywords in column B and some random text in the column A including the keywords in parenthesis. Is there a way for a formula to find all keywords in A2:A10 and then list them in cell C11 with commas in between.So the result would be one cell C11 showing BLUE,GREEN,BLACK,ORANGE, etc.I have been trying to mess around with index and match and while I could get one to pull up I don't know how to combine it.

Keyword Range.xlsx‎

View 5 Replies View Related

How Can Range Be Equal A Single Cell

Jun 24, 2014

I am reading this fomular and cannot understand it.

=SUMPRODUCT($Q$13:$Q$309,($P$13:$P$309=$D20)*1,($O$13:$O$309=H$8)*1)

Basically each input should be a matrix and the first one really is. However the other two are not and look unfamiliar to me.

View 1 Replies View Related







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