Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding extra text into an existing column | Excel Worksheet Functions | |||
Formula help : adding | New Users to Excel | |||
Adding an extra validation to this formula. | Excel Discussion (Misc queries) | |||
Adding a formula to a pivot table | Excel Discussion (Misc queries) | |||
DATABASE FORMULA | Excel Discussion (Misc queries) |