adding formula to existing database

[Guest]

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"

 

[Barb Reinhardt]

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&I1

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

 

[Guest]

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

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&I1

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

 

[Guest]

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 here:
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

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&I1

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