Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Country Boy
 
Posts: n/a
Default adding formula to existing database

Hi

I am trying to get the new Google Maps system up and running on a web site
and I have been told to prepare the data in the Excel file with inverted
commas
as in the example below, which gives customer ID, Category name, address,
town/city, county, post code and telephone number for the data to be
converted to XML and then uploaded into Google.

I have a large Excel file prepared before I was given this advice and to
individually do this to each entry will be excrutiatingly long and painful.
Does anyone know of a formula where I can add " " as in the example below. I
have been told this is tab and comma delimited but I have not heard of that
before and can't find it on my "save as". I am using MS Excel 2003.

"101","1","The Museum","Guildhall Square","Southampton","Hampshire","SO24
5BY","02380 512 6370"

--
Ta!

Country Boy
  #2   Report Post  
Barb Reinhardt
 
Posts: n/a
Default

1) Save a "pristine" copy of your file in case you make an error.
2) I assume your document is comma delimited. I also assume that your
data is in column A. If it's not, modify the suggestions based on the
column that it's in.
3). Select the column with the data that doesn't have the " in it as
necessary and the select DATA - TEXT TO COLUMNS - DELIMITED - Select ONLY
comma delimited and FINISH. Keep in mind that if there will be 8 columns of
data as you've shown it to us.
4) For this example, let's say

A1=101
B1=1
C1=The Museum
D1=Guildhall Square
E1=Southampton
F1=Hampshire
G1=SO24 5BY
H1=02380 512 6370

Create a helper column in I1 with "
Create a helper column in J1 with ","
In K1, enter the following

=I1&A1&J1&B1&J1&C1&J1&D1&J1&E1&J1&F1&J1&G1&J1&H1&I 1

When you have what you want, copy and paste the values for column I so that
you don't lose the data if you delete the preceeding columns.

Congratulations, you've learned about parsing and concatenation in EXCEL!
:^D


"Country Boy" wrote in message
...
Hi

I am trying to get the new Google Maps system up and running on a web site
and I have been told to prepare the data in the Excel file with inverted
commas
as in the example below, which gives customer ID, Category name, address,
town/city, county, post code and telephone number for the data to be
converted to XML and then uploaded into Google.

I have a large Excel file prepared before I was given this advice and to
individually do this to each entry will be excrutiatingly long and
painful.
Does anyone know of a formula where I can add " " as in the example below.
I
have been told this is tab and comma delimited but I have not heard of
that
before and can't find it on my "save as". I am using MS Excel 2003.

"101","1","The Museum","Guildhall Square","Southampton","Hampshire","SO24
5BY","02380 512 6370"

--
Ta!

Country Boy



  #3   Report Post  
Country Boy
 
Posts: n/a
Default

Bob

many thanks, it didn't work the first time as I used row 1 in the formula as
in I1 and forgot that contained the header (doh!) and then found it only
worked on the first row!! I simply hadn't pasted the " or the "," down to
cover all the data (doh! again). As soon as I had done that it worked fine.

I am still trying to find concatenation in the dictionary though!! Do you
get called a cheat in scrabble?

Thanks again.

Kerry

Country Boy


"Barb Reinhardt" wrote:

1) Save a "pristine" copy of your file in case you make an error.
2) I assume your document is comma delimited. I also assume that your
data is in column A. If it's not, modify the suggestions based on the
column that it's in.
3). Select the column with the data that doesn't have the " in it as
necessary and the select DATA - TEXT TO COLUMNS - DELIMITED - Select ONLY
comma delimited and FINISH. Keep in mind that if there will be 8 columns of
data as you've shown it to us.
4) For this example, let's say

A1=101
B1=1
C1=The Museum
D1=Guildhall Square
E1=Southampton
F1=Hampshire
G1=SO24 5BY
H1=02380 512 6370

Create a helper column in I1 with "
Create a helper column in J1 with ","
In K1, enter the following

=I1&A1&J1&B1&J1&C1&J1&D1&J1&E1&J1&F1&J1&G1&J1&H1&I 1

When you have what you want, copy and paste the values for column I so that
you don't lose the data if you delete the preceeding columns.

Congratulations, you've learned about parsing and concatenation in EXCEL!
:^D


"Country Boy" wrote in message
...
Hi

I am trying to get the new Google Maps system up and running on a web site
and I have been told to prepare the data in the Excel file with inverted
commas
as in the example below, which gives customer ID, Category name, address,
town/city, county, post code and telephone number for the data to be
converted to XML and then uploaded into Google.

I have a large Excel file prepared before I was given this advice and to
individually do this to each entry will be excrutiatingly long and
painful.
Does anyone know of a formula where I can add " " as in the example below.
I
have been told this is tab and comma delimited but I have not heard of
that
before and can't find it on my "save as". I am using MS Excel 2003.

"101","1","The Museum","Guildhall Square","Southampton","Hampshire","SO24
5BY","02380 512 6370"

--
Ta!

Country Boy




  #4   Report Post  
Country Boy
 
Posts: n/a
Default

Apologies Barb for putting Bob in my reply.

I work next to Bob and he was ridiculing me for getting the rows wrong the
first time and I inadvertently typed his name in my reply thanks. He didn't
know how to fix it himself though!

Just so you know what I am attempting, a similar site appears he
http://www.spireviews.com/island-map4.php

We need an XML data sheet that contains, what is now 260 different client
details (and rising) so they can be used in the Google maps and I dare say it
would not have been possible without your help.

Thanks again

Country Boy


"Barb Reinhardt" wrote:

1) Save a "pristine" copy of your file in case you make an error.
2) I assume your document is comma delimited. I also assume that your
data is in column A. If it's not, modify the suggestions based on the
column that it's in.
3). Select the column with the data that doesn't have the " in it as
necessary and the select DATA - TEXT TO COLUMNS - DELIMITED - Select ONLY
comma delimited and FINISH. Keep in mind that if there will be 8 columns of
data as you've shown it to us.
4) For this example, let's say

A1=101
B1=1
C1=The Museum
D1=Guildhall Square
E1=Southampton
F1=Hampshire
G1=SO24 5BY
H1=02380 512 6370

Create a helper column in I1 with "
Create a helper column in J1 with ","
In K1, enter the following

=I1&A1&J1&B1&J1&C1&J1&D1&J1&E1&J1&F1&J1&G1&J1&H1&I 1

When you have what you want, copy and paste the values for column I so that
you don't lose the data if you delete the preceeding columns.

Congratulations, you've learned about parsing and concatenation in EXCEL!
:^D


"Country Boy" wrote in message
...
Hi

I am trying to get the new Google Maps system up and running on a web site
and I have been told to prepare the data in the Excel file with inverted
commas
as in the example below, which gives customer ID, Category name, address,
town/city, county, post code and telephone number for the data to be
converted to XML and then uploaded into Google.

I have a large Excel file prepared before I was given this advice and to
individually do this to each entry will be excrutiatingly long and
painful.
Does anyone know of a formula where I can add " " as in the example below.
I
have been told this is tab and comma delimited but I have not heard of
that
before and can't find it on my "save as". I am using MS Excel 2003.

"101","1","The Museum","Guildhall Square","Southampton","Hampshire","SO24
5BY","02380 512 6370"

--
Ta!

Country Boy




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
adding extra text into an existing column Zach Excel Worksheet Functions 2 August 11th 05 05:26 PM
Formula help : adding Niki New Users to Excel 3 March 24th 05 07:43 AM
Adding an extra validation to this formula. Mindie Excel Discussion (Misc queries) 1 March 8th 05 12:49 AM
Adding a formula to a pivot table Tony Excel Discussion (Misc queries) 2 January 20th 05 11:27 AM
DATABASE FORMULA becky Excel Discussion (Misc queries) 1 January 20th 05 02:45 AM


All times are GMT +1. The time now is 08:45 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"