map problems

[casassa]

When using the map locator I am unable to get the right map of a city in Italy. It takes me to Africa instead of Italy. It has always worked before. Please help!

[Essgee]

1. Could be that there is a city with the same name in Africa.

2. Have you found this city in Italy before?

3. What you call a city might have been a small town or hamlet in the past centuries, but today has been absorbed into a larger town.

4. Look for the Province that the town is associated with. Then you might be able to find it.

5. You might tell us the name of the town and we will see if we can help you find it.

6. If you don't know the province, check out this site: http://www.italyworldclub.com/8000_italian_communes/

It also has a link to the frazioni...about 35,000 of them...you should be able to find it in the indexes...it is a start!

Let us know what we can help with.

PS. The first thing I did when I started was purchase an Atlas of Italy. It has come in handy and is about worn out. Sometimes it helps locate the place on the internet. Just a thought.

[Essgee]

Having trouble posting, hopefully this will not be posted already...but see no proof that it was:

1. Could be that there is a city with the same name in Africa.

2. Have you found this city in Italy before?

3. What you call a city might have been a small town or hamlet in the past centuries, but today has been absorbed into a larger town.

4. Look for the Province that the town is associated with. Then you might be able to find it.

5. You might tell us the name of the town and we will see if we can help you find it.

6. If you don't know the province, check out this site: http://www.italyworldclub.com/8000_italian_communes/

It also has a link to the frazioni...about 35,000 of them...you should be able to find it in the indexes...it is a start!

Let us know what we can help with.

PS. The first thing I did when I started was purchase an Atlas of Italy. It has come in handy and is about worn out. Sometimes it helps locate the place on the internet. Just a thought.

[casassa]

I'm not sure of problem. I type in Locana and Locana Torino pops up. I click on red pin and it takes me to map quest and Africa. ??? What am I doing wrong? Is it my computer?

[vj]

This may help too
Here's a pretty nice map site
I just look for the name under 'populated place'
http://worldmaps.web.infoseek.co.jp/italy_guide.htm
can view by 'terrain' or 'map'
Valarie

[jtorre]

>I click on red pin and it takes to map quest and Africa. ??? What am I >doing wrong? Is it my computer?

The map problem you've had has been there for quite a while. From the "Locations in Italy" database on this site, the red pin for the map of a location always goes to Africa. It used to work and show where in Italy the town was. I'm guessing that the map coordinates the database has no longer match the maps underlying the site.

JTorre

[BillieDeKid]

It's not your computer it does for my comune also.

Use mapquest and select international and Italy then put in the name, it works fine.

Hope this helps

[wldspirit]

I notified Edmondo of the problem with the locations map.
Hopefully he can resolve the issue, it's been some time since I
used the database, so I'm not sure when the problem originally
started.
Thanks for bringing this to our attention....

wldspirit

[misbris]

I have been a member for a year and a half and it has never worked for me. I just assumed it was always nonfunctioning.

[Edmondo]

The latitude and longitude of all records in the database seem to be destroyed.
It does not look very difficult to repair so I will try to fix it within a few days.

[Edmondo]

I need the help of an Excel expert to convert a cell with 4 numbers seperated by a space to 4 different cells:

"12 34 56 78" -> "12","34","56","78"
"1 23 4 56" -> "1", "23","4", "56"

Any volunteers who can post the correct formula?

[BillieDeKid]

Edmondo

This should help. I tried it and it works perfect. This is with MicroSoft Office 2007 Excel. If you have 2003 or 97 it should work almost the same way. Hope this helps.

Distribute the contents of a cell into adjacent columns

You can split the contents of one or more cells in a column and distribute those contents as individual parts across other cells in adjacent columns. For example, your worksheet contains a column of full names that you want to split into separate first name and last name columns.

Select the cell, range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.), or entire column that contains the text values that you want to split.
Note A range that you want to split can include any number of rows, but it can include no more than one column. You also should keep enough blank columns to the right of the selected column to prevent existing data in adjacent columns from being overwritten by the data that will be distributed. If necessary, you can insert blank columns.

On the Data tab, in the Data Tools group, click Text to Columns.


Follow the instructions in the Convert Text to Columns Wizard to specify how you want to divide the text into columns.
Note For help with completing all the steps of the wizard, see the topic, Split names by using the Convert Text to Columns Wizard, or click Help in the Convert to Text Columns Wizard.

Tip For an alternative method of distributing text across columns, see the article, Split text among columns by using functions


Regards,
Elizabeth

[BillieDeKid]

This is how to do it using functions......it's very long but useful. You would want example 2

Split text among columns by using functions

Text functions are useful for manipulating strings in your data, for example, distributing the first, middle, and last names from a cell into three separate columns.

This article demonstrates how to use combinations of the following text functions to extract and copy name components into separate cells.

Function Syntax
LEFT LEFT(text, num_chars)
MID MID(text,start_num,num_chars)
RIGHT RIGHT(text, num_chars)
SEARCH SEARCH(find_text,within_text,start_num)
LEN LEN(text)

Extracting name components
The key to distributing name components when you use text functions is the position of each character within a text string. The positions of the spaces within the text string are important because they indicate the beginning or end of name components in a string.

For example, in a cell that contains only a first and last name, the last name begins after the first instance of a space. Some names in your list may contain a middle name, in which case, the last name begins after the second instance of a space.

This article shows you how to extract various components from a variety of name formats. Click an example name in the following table to see the formulas for extracting the components from that name.

Note The formulas for each example may be easier to understand if you copy them to a blank worksheet.


How to copy an example

Create a blank workbook or worksheet.
Select the example text and formulas in the table.
Note Do not select the row or column headers.



Press CTRL+C.
In the worksheet, select cell A1, and then press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

--------------------------------------------------------------------------------

Example name Description First name Middle name Last name Suffix
1 Jeff Smith No middle name Jeff Smith
2 Eric S. Kurjan One middle initial Eric S. Kurjan
3 Janaina B. G. Bueno Two middle initials Janaina B. G. Bueno
4 Kahn, Wendy Beth Last name first, with comma Wendy Beth Kahn
5 Mary Kay D. Andersen Two-part first name Mary Kay D. Andersen
6 Paula Barreto de Mattos Three-part last name Paula Barreto de Mattos
7 James van Eaton Two-part last name James van Eaton
8 Bacon Jr., Dan K. Last name and suffix first, with comma Dan K. Bacon Jr.
9 Gary Altman III With suffix Gary Altman III
10 Mr. Ryan Ihrig With prefix Ryan Ihrig
11 Julie Taft-Rider Hyphenated last name Julie Taft-Rider


Example 1: Jeff Smith

--------------------------------------------------------------------------------

In this example, there are only two components: first name and last name. A single space separates the two name components.


1
2
A B C
Full name First name Last name
Jeff Smith =LEFT(A2, SEARCH(" ",A2,1)) =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))


Note In the following graphics, the highlight in the full name shows the character that the matching SEARCH formula is looking for.

First name
The first name starts with the first character in the string (J) and ends at the fifth character (the space). The formula returns five characters in A2, starting from the left.


--------------------------------------------------------------------------------

Use the SEARCH function to find the value for num_chars:

Search for the numeric position of the space in A2, starting from the left. (5)


--------------------------------------------------------------------------------

Last name
The last name starts at the space, five characters from the right, and ends at the last character on the right (h). The formula extracts five characters in A2, starting from the right.


--------------------------------------------------------------------------------

Use the SEARCH and LEN functions to find the value for num_chars:

Search for the numeric position of the space in A2, starting from the left. (5)

Count the total length of the text string, and then subtract the number of characters from the left to the first space, as found in step 1. (10 - 5 = 5)


--------------------------------------------------------------------------------


Top of Page

Example 2: Eric S. Kurjan

--------------------------------------------------------------------------------

In this example, there are three components in the full name: first name, middle initial, and last name. A space separates each name component.


1
2
A B C D
Name First Name (Eric) Middle Name (S.) Last Name (Kurjan)
Eric S. Kurjan =LEFT(A2, SEARCH(" ",A2,1)) =MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-SEARCH(" ",A2,1)) =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1))


Note In the following graphics, the highlight in the full name shows the character that the matching SEARCH formula is looking for.

First name
The first name starts with the first character from the left (E) and ends at the fifth character (the first space). The formula extracts the first five characters in A2, starting from the left.


--------------------------------------------------------------------------------

Use the SEARCH function to find the value for num_chars:

Search for the numeric position of the first space in A2, starting from the left. (5)


--------------------------------------------------------------------------------

Middle name
The middle name starts at the sixth character position (S), and ends at the eighth position (the second space). This formula involves nesting SEARCH functions to find the second instance of a space.

The formula extracts three characters, starting from the sixth position.


--------------------------------------------------------------------------------

Use the SEARCH function to find the value for start_num:

Search for the numeric position of the first space in A2, starting from the first character from the left. (5)

Add 1 to get the position of the character after the first space (S). This numeric position is the starting position of the middle name. (5 + 1 = 6)

Use nested SEARCH functions to find the value for num_chars:

Search for the numeric position of the first space in A2, starting from the first character from the left. (5)

Add 1 to get the position of the character after the first space (S). The result is the character number at which you want to start searching for the second instance of space. (5 + 1 = 6)

Search for the second instance of space in A2, starting from the sixth position (S) found in step 4. This character number is the ending position of the middle name. (8)

Search for the numeric position of space in A2, starting from the first character from the left. (5)

Take the character number of the second space found in step 5 and subtract the character number of the first space found in step 6. The result is the number of characters MID extracts from the text string starting at the sixth position found in step 2. (8 – 5 = 3)


--------------------------------------------------------------------------------

Last name
The last name starts six characters from the right (K) and ends at the first character from the right (n). This formula involves nesting SEARCH functions to find the second and third instances of a space (which are at the fifth and eighth positions from the left).

The formula extracts six characters in A2, starting from the right.


--------------------------------------------------------------------------------

Use the LEN and nested SEARCH functions to find the value for num_chars:

Search for the numeric position of space in A2, starting from the first character from the left. (5)

Add 1 to get the position of the character after the first space (S). The result is the character number at which you want to start searching for the second instance of space. (5 + 1 = 6)

Search for the second instance of space in A2, starting from the sixth position (S) found in step 2. This character number is the ending position of the middle name. (8)

Count the total length of the text string in A2, and then subtract the number of characters from the left up to the second instance of space found in step 3. The result is the number of characters to be extracted from the right of the full name. (14 – 8 = 6)


--------------------------------------------------------------------------------




Top of Page


Example 3: Janaina B. G. Bueno

--------------------------------------------------------------------------------

In this example, there are two middle initials. The first and third instances of space separate the name components.


1
2
A B C D
Name First Name (Janaina) Middle Name (B. G.) Last Name (Bueno)
Janaina B. G. Bueno =LEFT(A2, SEARCH(" ",A2,1)) =MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1)-SEARCH(" ",A2,1)) =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1))


Note In the following graphics, the highlight in the full name shows the character that the matching SEARCH formula is looking for.

First name
The first name starts with the first character from the left (J) and ends at the eighth character (the first space). The formula extracts the first eight characters in A2, starting from the left.


--------------------------------------------------------------------------------

Use the SEARCH function to find the value for num_chars:

Search for the numeric position of the first space in A2, starting from the left. (8)


--------------------------------------------------------------------------------

Middle name
The middle name starts at the ninth position (B), and ends at the fourteenth position (the third space). This formula involves nesting SEARCH to find the first, second, and third instances of space in the eighth, eleventh, and fourteenth positions.

The formula extracts five characters, starting from the ninth position.


--------------------------------------------------------------------------------

Use the SEARCH function to find the value for start_num:

Search for the numeric position of the first space in A2, starting from the first character from the left. (8)

Add 1 to get the position of the character after the first space (B). This numeric position is the starting position of the middle name. (8 + 1 = 9)

Use nested SEARCH functions to find the value for num_chars:

Search for the numeric position of the first space in A2, starting from the first character from the left. (8)

Add 1 to get the position of the character after the first space (B). The result is the character number at which you want to start searching for the second instance of space. (8 + 1 = 9)

Search for the second space in A2, starting from the ninth position (B) found in step 4. (11)

Add 1 to get the position of the character after the second space (G). This character number is the starting position at which you want to start searching for the third space. (11 + 1 = 12)

Search for the third space in A2, starting at the twelfth position found in step 6. (14)

Search for the numeric position of the first space in A2. (8)

Take the character number of the third space found in step 7 and subtract the character number of the first space found in step 6. The result is the number of characters MID extracts from the text string starting at the ninth position found in step 2.


--------------------------------------------------------------------------------

Last name
The last name starts five characters from the right (B) and ends at the first character from the right (o). This formula involves nesting SEARCH to find the first, second, and third instances of space.

The formula extracts five characters in A2, starting from the right of the full name.


--------------------------------------------------------------------------------

Use nested SEARCH and the LEN functions to find the value for the num_chars:

Search for the numeric position of the first space in A2, starting from the first character from the left. (8)

Add 1 to get the position of the character after the first space (B). The result is the character number at which you want to start searching for the second instance of space. (8 + 1 = 9)

Search for the second space in A2, starting from the ninth position (B) found in step 2. (11)

Add 1 to get the position of the character after the second space (G). This character number is the starting position at which you want to start searching for the third instance of space. (11 + 1 = 12)

Search for the third space in A2, starting at the twelfth position (G) found in step 6. (14)

Count the total length of the text string in A2, and then subtract the number of characters from the left up to the third space found in step 5. The result is the number of characters to be extracted from the right of the full name. (19 - 14 = 5)


--------------------------------------------------------------------------------




Top of Page


Example 4: Kahn, Wendy Beth

--------------------------------------------------------------------------------

In this example, the last name comes before the first name, and the middle name appears at the end. The comma marks the end of the last name, and a space separates each name component.


1
2
A B C D
Name First Name (Wendy) Middle Name (Beth) Last Name (Kahn)
Kahn, Wendy Beth =MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-SEARCH(" ",A2,1)) =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1)) =LEFT(A2, SEARCH(" ",A2,1)-2)


Note In the following graphics, the highlight in the full name shows the character that the matching SEARCH formula is looking for.

First name
The first name starts with the seventh character from the left (W) and ends at the twelfth character (the second space). Because the first name occurs at the middle of the full name, you need to use the MID function to extract the first name.

The formula extracts six characters, starting from the seventh position.


--------------------------------------------------------------------------------

Use the SEARCH function to find the value for start_num:

Search for the numeric position of the first space in A2, starting from the first character from the left. (6)

Add 1 to get the position of the character after the first space (W). This numeric position is the starting position of the first name. (6 + 1 = 7)

Use nested SEARCH functions to find the value for num_chars:

Search for the numeric position of the first space in A2, starting from the first character from the left. (6)

Add 1 to get the position of the character after the first space (W). The result is the character number at which you want to start searching for the second space. (6 + 1 = 7)

Search for the second space in A2, starting from the seventh position (W) found in step 4. (12)

Search for the numeric position of the first space in A2, starting from the first character from the left. (6)

Take the character number of the second space found in step 5 and subtract the character number of the first space found in step 6. The result is the number of characters MID extracts from the text string starting at the seventh position found in step 2. (12 - 6 = 6)


--------------------------------------------------------------------------------

Middle name
The middle name starts four characters from the right (B), and ends at the first character from the right (h). This formula involves nesting SEARCH to find the first and second instances of space in the sixth and twelfth positions from the left.

The formula extracts four characters, starting from the right.


--------------------------------------------------------------------------------

Use nested SEARCH and the LEN functions to find the value for start_num:

Search for the numeric position of the first space in A2, starting from the first character from the left. (6)

Add 1 to get the position of the character after the first space (W). The result is the character number at which you want to start searching for the second space. (6 + 1 = 7)

Search for the second instance of space in A2 starting from the seventh position (W) found in step 2. (12)

Count the total length of the text string in A2, and then subtract the number of characters from the left up to the second space found in step 3. The result is the number of characters to be extracted from the right of the full name. (16 - 12 = 4)


--------------------------------------------------------------------------------

Last name
The last name starts with the first character from the left (K) and ends at the fourth character (n). The formula extracts four characters, starting from the left.


--------------------------------------------------------------------------------

Use the SEARCH function to find the value for num_chars:

Search for the numeric position of the first space in A2, starting from the first character from the left. (6)

Subtract 2 to get the numeric position of the ending character of the last name (n). The result is the number of characters you want LEFT to extract. (6 - 2 =4)


--------------------------------------------------------------------------------




Top of Page


Example 5: Mary Kay D. Andersen

--------------------------------------------------------------------------------

In this example, there are two parts of the first name: Mary Kay. The second and third spaces separate each name component.


1
2
A B C D
Name First Name (Mary Kay) Middle Name (D.) Last Name (Andersen)
Mary Kay D. Anderson =LEFT(A2, SEARCH(" ",A2,SEARCH(" ",A2,1)+1)) =MID(A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1,SEARCH(" ",A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1)-(SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1)) =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1))


Note In the following graphics, the highlight in the full name shows the character that the matching SEARCH formula is looking for.

First name
The first name starts with the first character from the left and ends at the ninth character (the second space). This formula involves nesting SEARCH to find the second instance of space from the left.

The formula extracts nine characters, starting from the left.


--------------------------------------------------------------------------------

Use nested SEARCH functions to find the value for num_chars:

Search for the numeric position of the first space in A2, starting from the first character from the left. (5)

Add 1 to get the position of the character after the first space (K). The result is the character number at which you want to start searching for the second instance of space. (5 + 1 = 6)

Search for the second instance of space in A2, starting from the sixth position (K) found in step 2. The result is the number of characters LEFT extracts from the text string. (9)


--------------------------------------------------------------------------------

Middle name
The middle name starts at the tenth position (D), and ends at the twelfth position (the third space). This formula involves nesting SEARCH to find the first, second, and third instances of space.

The formula extracts two characters from the middle, starting from the tenth position.


--------------------------------------------------------------------------------

Use nested SEARCH functions to find the value for start_num:

Search for the numeric position of the first space in A2, starting from the first character from the left. (5)

Add 1 to get the character after the first space (K). The result is the character number at which you want to start searching for the second space. (5 + 1 = 6)

Search for the position of the second instance of space in A2, starting from the sixth position (K) found in step 2. The result is the number of characters LEFT extracts from the left. (9)

Add 1 to get the character after the second space (D). The result is the starting position of the middle name. (9 + 1 = 10)

Use nested SEARCH functions to find the value for num_chars:

Search for the numeric position of the character after the second space (D). The result is the character number at which you want to start searching for the third space. (10)

Search for the numeric position of the third space in A2, starting from the left. The result is the ending position of the middle name. (12)

Search for the numeric position of the character after the second space (D). The result is the beginning position of the middle name. (10)

Take the character number of the third space, found in step 6, and subtract the character number of “Dâ€

[Edmondo]

Thank you Elizabeth!

We are going a litttle off topic here but you gave me the hint I needed !!!
I am no excel expert and did not know the find function.

This is what I did and it gave me the result I wanted:

- take te length of the cell
- find the position of the first space in the cell
- first number = left (cell ; length - first space position - 1)
- new cell = right(old cell ; length - first space)

- take the length of the new cell
- find the position of the first space in the new cell
- second number = left (new cell ; length - first space position - 1)
- new cell = right(old cell ; length - first space position)

- take the length of the new cell
- find the position of the first space in the new cell
- third number = left (new cell ; length - first space position - 1)
- new cell = right(old cell ; length - first space position)

- take the length of the new cell
- find the position of the first space in the new cell
- fourth number = left (new cell ; length - first space position- 1)
- new cell = right(old cell ; length - first space position)

Now I can start to rebuild the database.

[Edmondo]

It should be fixed now, let me know the results.

I also noticed the text is not displayed (translated) correctly, this also I will try to fix a.s.a.p. but not today.