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


ADVERTISEMENT

Reformat Text In Single Column To Rows Using Delimiter

Jun 10, 2008

I am trying to convert text to rows but in the same column Take the following example:

Column A:
1-2-3
ab-cde-fg
54-ty-12345
the
waht i am looking is convert it to the following format: (the - is the delimiter)
Column A

1
2
3
ab
cde
fg
54
ty
12345

i have found the following VBA code on those forums but i don't know how to edit it to suit my needs

Sub Txt_To_Rows()
Dim arrText() As String
Dim varItm As Variant
Dim rngText As Range
Dim rngCl As Range
Dim i As Integer
Dim j As Integer
Dim x As Integer
Set rngText = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
j = 2...........................

View 2 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

Transpose Rows To Single Column

Feb 23, 2012

I'm trying to do something which I can't manage with traditional formulas and a macro might be required.

I have the following table:

Code:

Header1Header2Header3Header4Header5
1.00 6.00 11.0016.0021.00
2.00 7.00 12.0017.0022.00
3.00 8.00 13.0018.0023.00
4.00 9.00 14.0019.0024.00
5.00 10.0015.0020.0025.00

What I would need to do is take all column values and transpose is to rows, copying the header for every set, like:

ColumnA ColumnB
Header1 1.00
Header2 6.00
Header3 11.00
Header4 16.00
Header5 21.00

[Code] ...

View 8 Replies View Related

VBA / Transpose Each Row To Return One Single Column

May 11, 2012

What macro would convert this:

Excel 2007AB112234Sheet2

To this:

Excel 2007A51627384Sheet2

I need a macro please that works with a table of any size.

View 6 Replies View Related

Transpose Each Row Of X Columns Into Single Column

Apr 27, 2008

I need to combine data in 5 columns into 1 column as below. The columns to be merged are always identical in length up 20 000 lines.(Full example attached)
Raw data in columns A,B,C,D,E as below

A1 B1 C1 D1 E1
A2 B2 C2 D2 E2
Converted should be
A1
B1
C1
D1
E1
A2
B2
C2
D2
E2

View 7 Replies View Related

Text To Column With Single Column Transpose?

Aug 13, 2014

I have a file that I need to do a text to column separated by comma and then transpose results to a single column. See exaple below

File
AreaZone
0886518
1801315
1801413
1801524, 25
1804214, 16, 18

Results:

AreaZone
0886518
1801315
1801413
1801524
1801525
1804214
1804216
1804218

View 8 Replies View Related

Transpose Data In Single Cell From Horizontal To Vertical

Oct 28, 2011

Currently we are transposing data in multiple cells from horizontal to vertical & vice versa.

But when i try to transpose data which are in single cells seperated with semicolon or comma, im not able to perform the action.

Is there any VBA function or public function to perform the this action?

Example:

From

A 1Dog; Lion; Parrot; Bee; Snail

To

A 7Dog8Lion9Parrot10Bee

11Snail

Like wise i will have to do the same action for the following

A B1Dog; Lion; Parrot; Bee; Snail2Goat; Crocodile; Love Birds; Bug; Snake3Hen; Elephant; Peocock; Mosquito4Dog12; Tiger78; Flies5Cat11; Bug1506Chicken7

View 5 Replies View Related

Check Whether Transpose Ranges Are Equal In Single Cell

Apr 30, 2008

How a single-cell formula to check that 2 transpose arrays are equal.

For example, A1:A5 are {1,2,3,4,5}

AND

B3:B8 are {1,2,3,4,5}

Is there an array formula in C3 for example, that will check (i.e. say TRUE) if corresponding ranges are true i.e. check in this cell that A1=B3, A2=B4,...A5=B8.

View 9 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 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 View Related

Transpose Range From Column To Row?

Aug 20, 2013

I have code below that I need to take a copied range and Transpose the range from a column to a row.

Code:
Sub Search_Notes_Main()
Application.ScreenUpdating = False
Sheets("Analyst Main Template").Select

[Code].....

View 1 Replies View Related

Transpose Every Nth Cell Range

Apr 18, 2008

I am currently using the following code to copy data in a spreadsheet from a horizontal format to a vertical one, i.e
before -
data1
data2
after -data1 data2

Range("B5:B14").Select
Application.CutCopyMode = False
Selection.Copy
Range("N3:W3").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

I need to do this all the way down to cells B5000 and N5000 to ensure all data is copied but obviously this makes for a lot of code. Is there any way I can use a For statement to auto increment 4 variables to replace the absolute cell references? I have attached the sheet I am trying to wokr on for reference.

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

Transpose Multiple Columns For Single Row

Feb 12, 2014

Before
XYZ1
XYZ2
XYZ3
XYZ4
XYZ5
XYZ6
XYZ7

After
XXXXXXXYYYYYYYZZZZZZZ1234567

[Code] .....

View 10 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

Transpose Multiple Rows Of Data To Single Row?

Jul 24, 2014

I have a set of data that I need to change the "layout" of. I've had similar situations before, but this one is just killing me. Basically, my data is for item pricing. It is represented as

Item, QTY, Price
A,1,1.25
A,10,1.1
A,25,1
A,100,0.9
B,1,1.25
B,10,1.1
B,25,1
B,100,0.9
C,1,1.25
C,10,1.1
C,25,1
C,100,0.9
Item D,Qty1,P1
Item D,Q2,P2
Item D,Q3,P3
Item D,Q4,P4

However, I need it in the following format:

A,1,10,25,100,1.25,1.1,1,0.9
B,1,10,25,100,1.25,1.1,1,0.9
C,1,10,25,100,1.25,1.1,1,0.9
Item DQ1Q2Q3Q4P1P2P3P4

As a note: there is a maximum of 4 Price/QTY breaks, so the script can be hard coded for that. When I tried this, I had it looking at the Item column, finding out how many breaks there are for a specific item and then doing a loop to extract the qty and price to a single row in the format shown above. It worked for the first 2 items, but then the loop got throw off. I will see if I can reproduce the code for that.

View 4 Replies View Related

Split Some Text In Range If It Does Not Have Delimiter?

Apr 24, 2012

How do i split some text in range if it does not have a delimiter? I would like to have a comma after each letter/number or have the option to split to individual cells

I know how to use the vba split function with a delimiter but cant figure this out.

I.e susanna
S,u,s,a,n,n,a.

View 4 Replies View Related

After Splitting Data In One Column Using Delimiter?

Jan 29, 2013

Currently, I have an excel sheet of names and addresses. In my address column, many of the addresses have carriage returns separating information. I want to divide this information by carriage returns and parse it into different columns.

I have already tried what I believe is the correct method: I go to Data>Text to Columns. I choose "delimited." Then I enter [Alt 0101] under "other" and make sure that is the only option checked. When I finish, only *some* of my data is parsed into corresponding columns by the delimiter while some isn't.

View 2 Replies View Related

Split Column On 2nd Occurrence Of Delimiter

Feb 28, 2007

I have one column in a worksheet that I want to split into 2 columns. If there is one "(" in the cell, I want to split at that point, but if there are two "(" 's, I want to split at the second occurrence. I can use the Like operator to identify the cells, but is there a way to specify which delimiter in a sequence of identical delimiters to split at? Here are templates (not actual data) of the two type of cells:

Name1 (ABC) (Name2, Name3)
Name4 (Name5, Name6)

For the first, I want to split on the second "(", before Name2, and for the second, I want to split on the first "(", before Name5.

View 4 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

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

Concatenate Multiple Rows In A Column Using Delimiter Based On Changing Value

Aug 16, 2013

I am attempting to concatenate the dept numbers for each user in a list. For example, the first user listed below is associated with 6 depts. I would like the resulting macro to concatenate the dept's into one cell next to the Name.

Below is an example with the solution I am attempting to produce.

Sample Table

Name
Dept

Abbruzzese,James L
188100

[Code] ......

Output

Name
Dept

Abbruzzese,James L
188100, 231100, 600377, 600656, 600663, 600708

Abdi,Salahadin
600607, 600670, 600878, 600879, 710432, 710432, 710435

View 2 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







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