Convert One Column Of Addresses Into Three Columns When Text To Be Grouped Is Not Even

Jan 10, 2014

I have checked to see if this has been answered before and can not find an exact reply. My problem is that I have a combined column of address: Suburb, State, Postcode - which needs to be broken into three which not be done accurately using tet to column or fx - left mid or right. The issue is that the Suburb could be made up of one or two Words with a space between. So I can not separate using text to columns as in some cases the suburbs comprised of two words will put the second word in the "state" column. It can also not be done using Left, Right,Mid, as they number of characters differs in each line. example problem with columns to text.jpg


View 12 Replies


VBA Code To Convert Multiple Rows As Column Header Grouped By Unique Key

Jul 24, 2014

I have data in excel sheet in the below format:

Existing view.png

How to write a VBA code or Macro to get it in below format:

Required View.png

Timestamp column is the unique key.

View 1 Replies View Related

Convert Column B As Multiple Column Titles And Move Data In Column C Into New Columns?

Jun 30, 2014

I have a excelsheet that looks like this:

Column A | Column B | Column C
Los Angeles | Fire Dept | 3
Los Angeles | Health Services | 12
New York | Fire Dept | 8
New York | Health Services | 22
New York | Internal Services | 100
New York | Public Works | 7
Chicago | Health Services | 15
Chicago | Public Works | 56
Chicago | Social Services | 4

And I am trying to make it look like this:

Fire Dept
Health Services
Internal Services
Public Works
Social Services

Los Angeles

New York




View 8 Replies View Related

Sorting IP Addresses - How To Convert Them

Jul 10, 2006

Any quick way to sort this list of IP addresses? comes after when Excel sorts the data. I understand the problem is that these are not numbers, they are text.

View 11 Replies View Related

Check If Columns Are Grouped (VBA)

Dec 5, 2008

How can I check (using VBA) if 2 columns (F and G) are already grouped.

View 7 Replies View Related

Calculate Sum Of Grouped Columns

Feb 8, 2008

I have a spreadsheet where columns belonging to the same topic are grouped together. For every group I want to calculate the sum of numbers in a row. How can I get all the columns belonging to a group?

View 8 Replies View Related

Convert Addresses From PDF File Into Excel

Sep 10, 2012

I am having a challenge at work. We have a client that emailed us an PDF file with addresses. There are over 200 pages and each page has 30 addresses (3 coloumns and 10 rows). When I try to copy and paste the addresses into excel, the addresses are all next to eachother and are pasted into excel as you would see an address on an envelope. But I need the parts of each address in a seperate column.

For example
column 1: name of company;
column 2: name of recipient;
column 3: address,
column 4: city;
column 5: state;
column 6: zip

View 4 Replies View Related

Charting Hidden / Grouped Columns?

Mar 18, 2014

I have several years worth of monthly data that I would like to keep hidden so it is printer friendly but I also want to have a chart of all that data... Is this possible? Only idea I have is to basically recreate the data sheet but I don't really like that solution since it adds steps to the process and increases the file size...

View 2 Replies View Related

VBA Code - Expand / Collapse Grouped Columns

Feb 10, 2014

Currently, Columns EFG are grouped.

I have a userform with checkboxes. With the click of check box, i would like for it to do the following.

If checked, it will expand the group. If unchecked, it needs to stay collapsed.

Here is an example of code i currently have and is not working.

[Code] ......

View 2 Replies View Related

Text To Columns And Convert Them To Decimal

Sep 2, 2009

I got following Hex data in text format, I need change them to decimal,

0324 00BF 0000 0000 030C 00C6 0000 0000
0305 00C7 0000 0000 02FE 300F 040B 4753
4D4D 5352 4E4D 5343 4E31 0030 0F0A 0101

firstly I did txt to columns, then I found some data changed to scientific format which is not I wanted as I could not convert it using function HEX2DEC.


if I set column data format to text when I doing Text to Columns operation then all data will be text, so I still can not convert them to decimal.

View 4 Replies View Related

TXT File- Convert Using Text To Columns

May 15, 2007

I have a .txt file which i need to convert using text to columns in excel, obviously this is simple, however my .txt file is 325000+ rows of data

Is there anyway I can Excel can cope with this amount of data, I know that my row limitation is 65536, can i spread the data across multiple sheet tabs?

View 9 Replies View Related

Convert Text To Columns For All The Sheets In A Workbook

May 31, 2009

I have a workbook with 40 sheets, I want to convert column A using Text to Columns I have recored the method as below

View 5 Replies View Related

Convert Text File To Structured/Sorted Columns

Oct 21, 2006

I am very new to VBA and have found a task out of my programming abilities using VBA.

I need code that will take a text file that begins as this shown in textfile.jpg

and ends up like the following attached picture of the results spreadsheet group.jpg

View 9 Replies View Related

Convert Single Column To Multiple Columns

Dec 6, 2007

How can I sort a single column of data to multiple columns of data. e.g. I want to convert ~1800 rows of data from 1 column to 3 or 4 columns.

View 14 Replies View Related

Convert Multiple Rows To Columns And Add Column Headers

Oct 17, 2009

I'm currently faced with a spreadsheet that has data formatted like this:
1 RandomRowofData1
2 RandomRowofData2
3 RandomRowofData3
4 RandomRowofData4
5 RandomRowofData5
6 RandomRowofData6
7 RandomRowofData7
8 RandomRowofData8
9 RandomRowofData9

Every 9 rows, a new "set" of data repeats itself (wow, this is so hard to put into words)....

I need to figure out a way to get the data in column "A", every 9 rows, to transpose itself into 9 separate columns.

View 4 Replies View Related

Convert Name / Address Phone In One Column To Multiple Columns

Feb 16, 2012

I have a long list of names, address, city state zip, and phone number, followed by the next name, address, etc. I need to now be able to bring each one into it's own column.

David Smith
123 Main St.
Denver, CO 12345
Joe Blow
345 Happy Ave.
Oakland, CA 34567

I need to convert it so that I would have

David Smith 123 Main St. Denver, CO 12345 123.456.7890
Joe Blow 345 Happy Ave. Oakland CA 34567 567.890.1234

View 2 Replies View Related

Convert 2 Column List To Multiple Related Columns

Nov 28, 2007

I have a long list of 2 columns containing data as follows:

col A col B
country1 date1-1
country1 date1-2
country1 date1-3
country2 date2-1
country2 date2-2
country3 date3-1
country4 date4-1
country4 date4-2
country4 date4-3

which I would need to move to get one row per country in column A with all related values from col B in multiple columns on a single Row, e.g....

View 9 Replies View Related

Select 1st Empty Cell In Column That Is Grouped

Feb 27, 2008

I am trying to paste data into a sheet immediately following the last weeks data. I went to use an offset function like :

ActiveCell.Offset(1, 0).Select

However, each weeks data is "Grouped" and therefore the .end(xldown) only takes me to the bottom of the visible rows, and the offset function selects one of the hidden cells.

View 5 Replies View Related

Convert Column To Text?

Mar 17, 2013

how to convert the column to text. For Eg: i have column like


so i want in single row like..4352,2356,0012,0452...

View 10 Replies View Related

Convert Column Of Time To Text Value

Jul 23, 2012

I'm wondering how to code the conversion of a column (columns A, D, E and Q) from an Excel time to simple text. ie. time formatted 3:45 pm to text 3:45 pm.

View 9 Replies View Related

Macro To Convert Text To Column?

Jul 10, 2013

I am facing difficulty in creating a macro which will convert column A text into 2 separate columns (A & B) with tab & colon as the delimiters.

View 8 Replies View Related

Pivot Showing Percentage In Grand Total Grouped By First Column?

May 16, 2014

i'm trying to crack what should surely be a simple problem. Attached is my spreadsheet.

As you can see I have 3 columns of detail, with totals spread over a number of months.

I want to be able to display the percentage of the Resource total for each other grouping.

For example, in my spreadsheet you can see resource Mick Arber at the top. I want to be able to see each of his rows as a percentage of HIS total.

View 1 Replies View Related

Addresses In Different Format To Columns

Apr 10, 2014

I have tons of addresses that I need to separate into different columns. It should be easily done by using Text to Column but the format is different so it didn't work.

1198 W 1520 N
CLINTON, UT 84015-5301
1198 W 1520 N


BRIGHTON, CO 80603-8481

The addresses are copied from a website so it is formatted as 2 lines. Tried to copy and paste (values only) to another sheet and the format was like this:

1198 W 1520 NCLINTON, UT 84015-5301

No space between "N" and "CLINTON".

View 2 Replies View Related

How To Convert A Text From One Column To Another Column In A Different Way

Apr 26, 2014

I need to convert a text from one column to another column in a different way.

is there any automated way or function to convert

this - >This Text format to url format


this - >this-text-format-to-url-format

View 5 Replies View Related

Convert From Text To Column And Paste The Converted Data In Other Sheets?

Apr 18, 2014

I want some vbs code on my 'Copy All' button in Email Data sheet which will do my work. Its a bit lengthy but who knows the concept will find it easy.

I have one sheets -

Sheet attached - Worksheet .xlsm

Workshet.xlsm [Data in 'Mod' sheet i)should convert from text to column and paste in 'Row Data' sheet ii) then transfer form Row Data sheet to 'Email Data' sheet 'Mobile Data' sheet and 'Other Data' sheet when Copy All button is clicked in Email Data sheet.

There should be some logic which will fetch appropriate date from 'Mod' sheet to the respective sheet . We can have some kind of logic like

If 'Register' found then take the value of register and workstation and paste in Email Data sheet.

All data below should be fetched from Row Data sheet which is blank now and will get the data when we click the Copy All button ,

1st Register Count:

Below Register Count (*) i.e. 16 If workstation count present copy to E15 in Email Data sheet in Table B

Below Register Count (*) i.e. 9 If register count present copy to E16 in Email Data sheet Table B

2nd Register Count:

Below Register Count (*) i.e. 8942 If workstation count present copy to C15 in Email Data sheet in Table B
Below Register Count (*) i.e. 2883 If register count present copy to C16 in Email Data sheet in Table B

3rd Register Count:

Below Register Count (*) i.e. 0 If workstation count present copy to E18 in Email Data sheet in Table B

Below Register Count (*) i.e. 1 If register count present copy to E19 in Email Data sheet in Table B

4th Register Count:
Below Register Count (*) i.e. 98 If workstation count present copy to C18 in Email Data sheet in Table B
Below Register Count (*) i.e. 999 If register count present copy to C19 in Email Data sheet in Table B

B32:B44 will be pasted in C29:C41 in Email Data sheet in Table C
i.e. 10 to 22 values should match in corresponding 10 to 22 values in Email Data sheet , if not not any value then 0

B50:B62 will be pasted in B29:B41 in Email Data sheet in Table C
i.e. 10 to 22 values should match in corresponding 10 to 22 values in Email Data sheet , if not not any value then 0

View 1 Replies View Related

Moving Data (Addresses) From Rows To Columns

Dec 12, 2008

I have address data all in column A (each paret of the address is on a separate row) which I need to move to separate columns on one row for each address:


Mr Bloggs (row 1)
123 The Avenue (row2)
The town (row 3)
The postcode (row 4)

Mrs Smith (row 6)
456 The street (row7)
The postcode (row 8)

needs to be: (I have used || to indicate different columns)

Mr Bloggs || 123 The avenue || The town || The postcode
Mrs Smith || 456..... || .......

The problem you see from the above is that they are not always consistent ie. the postcode (zip) isn't always 3 rows below the name for example. Also although generally there is a blank row between each record (this may sometimes be 2) and equally there may also be times when there is a blank line in the address.

Any ideas on how to do this.... I have 1000's of records and really can't afford to go through each one making it consistent before moving it to columns?

View 7 Replies View Related

Summarize Columns, Based On Unique Value, Into 1 Sheet With Cell Addresses

May 25, 2008

I have the following code always throwing this error message: "subscript out of range". When i run the macro with worksheets that only have 10 rows each, it's running fine. I suspect some issues on the line: a = Range("a1"). CurrentRegion.Resize(, 3) - the debug always throws the error before entering the loop. This macro is supposed to find the lowest price among 3 spreadsheets by matching the "Code" column and sending the final result into a new spreadsheet. But since im trying to run the macro on spreadsheets holding over 6000 lines each, this error is appearing.

Sub kTest_v2()
Dim a, w(), i As Long, z, dic As Object
Dim MySheets, ws As Worksheet, MinPrice
MySheets = Array("Sheet1", "Sheet2", "Sheet3") 'change to suit
Set dic = CreateObject("scripting.dictionary")
dic.comparemode = vbTextCompare
For Each ws In Sheets(MySheets)
With ws
'assume Col A Country, Col B Code and Col C Price
a = Range("a1").CurrentRegion.Resize(, 3)
End With
For i = 2 To UBound(a, 1) 'assume column heading. if not replace 2 with 1
If Not IsEmpty(a(i, 1)) Then
If Not dic.exists(a(i, 2)) Then
Redim w(1 To 4): w(1) = a(i, 1) 'Country
w(2) = a(i, 2) 'Code
w(3) = a(i, 3) 'Price
w(4) = Cells(i, 1).Address(external:=True) 'cell address
dic.Add a(i, 2), w

View 7 Replies View Related

To Populate Three Columns With Text Based On Text Of Another Column

Dec 16, 2009

I am having a trouble in Excel sheet.My column A has a drop down list with text- possible, not possible, not required.Based on the text, i need to populate texts in columns B, C and D.

For example

Column A drop down selected is "possible"
then B coulmn should automatically populate "1-3"
C should populate with "3-5"
D should be "5-7"

I am using MS excel 2007.

View 9 Replies View Related

Changing Email Addresses To Text?

Mar 12, 2014

I copied a series of email addresses that I would like to convert into text. The email addresses, when hovered over, have a mail to: in the address. If I remove the hyperlink, all I get is the person's name but not the actual email address. How can I get just the email address?

View 8 Replies View Related

Extract Email Addresses From Text

Aug 24, 2007

I have a report that spits out a lot of data in one column. I would like to be able to extract the email address out of all that data. The data looks like this: ...

View 8 Replies View Related

Copyrights 2005-15, All rights reserved