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.

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

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


ADVERTISEMENT

Convert Address List To Multiple Columns

Apr 10, 2009

I'm looking for a way to convert an old church address list that is formatted in a single column. There are no blank rows, but the amount of rows for each entry can vary. For instance, there could be as little as 3 rows of data (church name, address, city/state/zip) or more if email addresses and websites are provided.

The data is currently in the format below (notice how one entry has a website while the other does not).

First Church
102 Main Street
Dallas, TX 12345
email@whatever.com
Second Church
500 Second Street
Austin, TX 12376
email2@whatever.com
http://www.boguswebsite.com

So I'm looking for the data to be formatted like the following:

First Church 102 Main Street Dallas, TX 12345 email@whatever.com
Second Church 500 Second Street Austin, TX 12376 email2@whatever.com http://www.boguswebsite.com

I was able to find the following code from a Google search, but it can't dynamically adjust the range.

Sub x()
Dim rng As Range

Set rng = Range("A1").Resize(5)
Do Until IsEmpty(rng.Cells(1, 1))
rng.Copy
Cells(Rows.Count, "B").End(xlUp).Offset(1).PasteSpecial Transpose:=True
Set rng = rng.Offset(5)
Loop

End Sub

I think what I need to make this code work is a way to dynamically adjust the range so that it can determine when to move to the next row of data. Static ranges break the process due to the amount of data being provided not being uniform. What I was thinking is that I could use the word "church" as a start point and end point in a loop so the script knows when to jump to the next row and begin copying the proper number of columns. I'm just not sure how to accomplish this in vba.

View 9 Replies View Related

Convert Long Address List To Multiple Columns

Nov 22, 2007

I've been given a long list of mailing addresses in Excel in formatted as a single column, each address using three rows with no blank rows. Example:

John Doe
123 Maple St. #2
Cambridge, MA 02139
Jane Smith
321 Elm St. Apt 24
Austin, TX 34557
etc.
etc.

I need to get this data into the form ...

John Doe 123 Maple St. #2 Cambridge MA 02139
Jane Smith 321 Elem St. Apt. 24 Austin TX 34557

I would think this has been done, but I've searched the web and this forum without success.

View 3 Replies View Related

Separating Mailing Address Info From 1 Column To Multiple Columns?

Dec 18, 2007

Whoever created my customer contacts Excel sheet prior to my arrival entered all of the contacts address information into one cell and in order for me to put this info into Access, I need them seperated. For example:

10000 X Street Louisville, KY 40291 is in cell E2

I would like it to read:

10000 X Street in cell E2
Louisville in cell F2
KY in cell G2
40291 in cell H2

They also did this with phone numbers (ie. desk# / cell# / fax#).

There are over 1000 contacts in this sheet, so it would take forever to split these apart row by row.

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

New York
8
22
100
7

Chicago

15

56
4

View 8 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:
A
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 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

Separating Name And Address Phone Number With Odd Delimiter?

Mar 18, 2013

I have the following text, in which I need separated into three columns.

Column A) Company Name
Column B) Address
Column C) Phone Number

I was thinking perhaps I could replace "-------" to just one "-" and use that delimiter to separate the phone number.

Then Use *** Company Name Here *** --> *Number Variable* and filter the rest..

But I don't see it being able to work.Here is the text I'm looking to format (Mind you there is about 8,000 Records.
And, it's all formatted like below. (Company Name, phone number being on different lines and phone number being separated by dash's

[Code].....

View 8 Replies View Related

Macro That Will Convert Phone #s

Mar 27, 2009

I'm needing a macro that will convert a column of 10 digit phone numbers and break them out or convert them from a XXXXXXXXXX format to a XXX XXX XXXX format.

I want to be able to copy and paste a long column of 10 digit numbers then have the macro run down the column and cut or copy the area code (first 3 digits of the 10 digit string) - move it two columns to the left - paste it into that cell - return then cut the next 3 digits - move it one column to the left, and paste it.

View 4 Replies View Related

Multiple Web Queries Where Address For Each In Column

May 19, 2008

Lets say I have some web addresses in column A (Sheet1) and I want retrieve data from all those sites to another worksheet (Sheet2). Data from 1st site should be put to Sheet2!A1, from 2nd site to Sheet2!A51, from 3rd site to Sheet2!A101 etc.

There are some similarities with this thread: Dynamic Web Query From Cell Values, but I don't want the data to be on separate sheets and as my programming skills in Excel are rather limited, I failed to modify the solution given in there.

View 9 Replies View Related

Convert Row To Multiple Columns

May 20, 2007

I am work a worksheet that contain the data like that
A B
NUM1 Jene,Joly
NUM2 Jene,Selo,Diff
MUM3 Tino,Selo

this worksheet has over 1,000 data.
i want get the result in sheet2 as below:
A B
NUM1 Jene
NUM1 Joly
NUM2 Jene

i write the code
[PHP]Sub test()
Dim i As Integer
Dim arr() As String
Application. ScreenUpdating = False
On Error Resume Next
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
arr = Split(Cells(i,2), ",")
For J = 0 To UBound(arr)
With Worksheets(2)
LASTROW = .Cells(Rows.Count, 2).End(xlUp).Row + 1
.Cells(LASTROW, 1) = Cells(i, 1)
.Cells(LASTROW, 2) = arr(J)
End With
Next J
Next i
Erase arr
Application.ScreenUpdating = True
End Sub
[/PHP]

the defult of this code is that it will take long time to get the result.so,i want to know how to make the code run fast.

View 9 Replies View Related

Convert Multiple Columns To Single Rows

Feb 27, 2007

I have a very large sheet of all US zip codes by county name. Unfortunately, the document builder decided to list up to EIGHTEEN columns of zip codes per county name... I assume to make it easier to look at.

I now need to rebuild the sheet to have one column of county names and *one* column of zip codes, which will be a nearly impossible task if I don't find a way to automate the conversion.

Attached is a sample... sheet 1 is my initial state (except here its 5 columns rather than 18), and sheet 2 is my hoped for end state. Notice that the zip codes can, but don't always, fill every column allotted.

View 3 Replies View Related

Convert/Transpose Multiple Groups Of Rows Across Columns

Mar 9, 2009

How to convert multiple Rows recors to a single row record in a Notes(csv) format? Have update my xls file. My source is in the below format(Source.xls):

GroupName_A,Name_A
GroupName_A,Name_B
GroupName_A,Name_C
GroupName_B,Name_D
GroupName_B,Name_E
GroupName_B,Name_F
GroupName_B,Name_G
GroupName_B,Name_H
GroupName_B,Name_I

I want to convert it to a CSV file where by it can be import to Lotus Notes (output.xls):

1,1,Group,GroupName_A,"Name_A,Name_B,Name_C","CN=John Sam/OU=FIN/OU=staff/O=IBM,CN=Mary Flow/OU=FIN/OU=staff/O=IBM",CN=John Sam/OU=FIN/OU=staff/O=IBM
1,1,Group,GroupName_B,"Name_D,Name_E,Name_F,Name_G,Name_H,Name_I","CN=John Sam/OU=FIN/OU=staff/O=IBM,CN=Mary Flow/OU=FIN/OU=staff/O=IBM",CN=John Sam/OU=FIN/OU=staff/O=IBM

As you can see only GroupNameN, and Name_N are varibles, the rest of the fields are static. note that there is opening and closing quota for column "E" and "F" in output.xls

View 5 Replies View Related

Convert Multiple Columns To Rows Including Blank Cells

Apr 11, 2014

I currently have a spreadsheet that I had to convert from multiple rows to columns:

[URL]

Now I need a script to change the data so that each column is now in row format, (see attached spreadsheet).

View 5 Replies View Related

How To Combine Two Columns To Make Phone #s.

Feb 1, 2010

I have two columns of numbers. The first is the area code and second is the phone number. How can I combine the two columns to make one with both: as in area code-phone number?

View 3 Replies View Related

Columns To Phone Numbers Format

Feb 1, 2010

Just got an answer to my phone number problem of converting two columns of numbers
to a single columns with the form XXX-XXXXXXX. Used =A1 & "-" & B1.

Works perfectly one one spread sheet and not at all in another.

When I enter the formula and hit return, instead of the correct result, all I get is the formula.

The format of the columns is the same on both spread sheets.

View 4 Replies View Related

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

COMBINED ADDRESS SUBURBSTATE??POSTCODE??POSTCODE??
ALTONA NORTH VIC 3025ALTONANORTHVIC 3025
BONDI NSW 2026 BONDINSW 2026
WOOLLOONGABBA QLD 4102WOOLLOONGABBAQLD4102
TOOWONG QLD 4066 TOOWONGQLD 4066
NOVAR GARDENS SA 5040NOVARGARDENSSA 5040

View 12 Replies View Related

Excel 2010 :: Convert Multiple Columns To Rows Depending On Input Value

Jul 6, 2014

I have lot of data in Excel 2010 which I wish to bring in Columns using a Macro depending on the input value which the macro should prompt me. For E.g.:

A1B1C1D1E1F1G1H1I1J1
12345678910
12345678910
12345678910
12345678910
12345678910

If I select data from A1 and J1 (in practical it will be more Columns) the Macro should prompt me how many Columns would be the output on Master Sheet. If the input is 2 then it should create an output Sheet "Master" and should show the following result

A1B1
12
12
12
12
12
34
34
34
34
34
56
56
56
56
56
.. ..
.. ..

It after selection I run the macro and input 3 then the output should go in three columns (A1,B1,C1) one below other. If 4 is Input then 4 Columns (A1,B1,C1,D1) will come below each other so on and so forth.

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

Convert IP Address To IP Range

Jan 29, 2014

I have a list of random I.P. Addresses i.e. 192.168.1.0, 172.19.16.0 etc

i would like to copy them into another cell but change them to a range. i.e.

192.168.0.1-192.168.1.254
172.19.16.0-172.19.16.254

View 5 Replies View Related

Convert Names To Email Address?

Mar 28, 2014

I've two columns, one being the first name and the second being the last name. Is there a way that I can convert these two columns into the correct format for the email address ? Rather than having to manually do this.

View 4 Replies View Related

Can't Convert Text To A Cell Address

Mar 12, 2010

Assume cell A1 contains a name and A2 contains the text "D7".

How would the code look like that
1. reads the contense in cell A2 (which is D7),
2. converts it (D7) to a cell address and finally
3. copies the content in cell A1 to the cell address found in A2 (in this case D7)

To illustrate I attached an Excel file.

View 8 Replies View Related

Export To Csv And Then Convert To Address Labels

Aug 18, 2009

I'm trying to export to csv and then convert to address labels. I know I could do this with a macro, but I have no idea about how to write them, or even how to get it to lay out the way I want. I need to do it so I can send out all the save the dates for my wedding. I don't want to do it in word (complicated story to do with it not working with the labels I've got).

The columns are layed out like this, with example addresses:
Title,Forenames,Surname,House Name/Number,Road,Area,Town/City,County,Postcode,Country
Mr,Alan,Davies,23,Malvern Road,Sheperton,Leicester,Leicestershire,LE2 3BY,UK
,Jason,Robson,3,Wake Green Road,,Birmingham,,B13 9QD,....................

View 5 Replies View Related

Function To Convert Long Value Into IP Address

Jul 29, 2014

Any function to convert a long value into an IP address. I have an extract from a mySQL dbase that has the ip addresses in long format. I need to reverse them back to IP Addresses.

View 1 Replies View Related

How Can I Convert Web Address Book To Sheet

Jul 5, 2007

I am trying to take an address book on the internet and copy/paste it into excel- not hard right? Ok, so that is done. The problem I have is that there are over 2000 addresses- and they all copy straight down into one column. I know how to transpose the information- but come on- there are over 2000. that seems like a LOT of work. So my first question would be is there a way to do this all at once? For each company/name/address/phone I need one row per company.

View 9 Replies View Related

Convert Text Address To Cell

Apr 6, 2007

I have a textbox with an address (ie "$C$3"), how do it then activate the correct cell (ie C3). Flash ActionScript has a function that would do this, eval(). I know VBA has a val() function, but I think thats different all together.

View 2 Replies View Related

4 Column Phone List

Aug 4, 2008

I have just created a phone list of about 70 lines on a single A4 sheet
A B C D
Name No Name No

One of the PA's now wants to know if she deletes a Name and No from say column A & B
can I make it all reconfigure to eliminate the blank spce.

So basically names fill column A & B first then overflow onto Columns C & D.

I think a macro is the only way to go here but the PA isn't keen.

View 9 Replies View Related

Convert Multiple Column Into 1 Column

Sep 16, 2009

to move data from multiple columns and one single column

COMPLIMENTUNBLOCKSINCEWITHSHEWORKINGFROMTIMEONSTAFFNAMEEXPERIENCERECEIVEDWORKINGSHOPSUPPOSEDFORATPLANSONGS

into 1 single column like this.

COMPLIMENTSHEONRECEIVEDFORUNBLOCKWORKINGSTAFFWORKINGATSINCEFROMNAMESHOPPLANWITHTIMEEXPERIENCESUPPOSEDSONGS

Can we do this with a formula?

View 9 Replies View Related

Convert Email Address In Cell Into Link?

Jul 22, 2014

I have a simlple macro to add a new customer to a list using a form. One of the columns of the datra beig added is an email address. When entering the data the email address just appears as text, but i want it to be the email address link you could click on and it opens up an outlook email. Just clicking into the cell and tabbing out changes this into the format i want, but wondering how to i get the macro to do this?

View 3 Replies View Related







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