How can I concatenate initial part cell 1 with all

Question

Problem

In Excel I have three columns with names for old governmental areas. These have been standardised to show the main name for the area and the old name in separate columns. Now I would like to display their full name automatically in a separate column using concatenate or any other suitable function.

The layout is as such:

• List item
• Col. E: Official name of area
• Col. F: Old name for area
• Col. G: Full name for area, as printed on original document. This contains what I desire for the concatenation.

It is a problem that some names have a genitive-s added to them, as shown in the example below:

     E                              F           G
2    Hobøl Thinglag                 Hobøle      Hobøl (Hobøle) Thinglag
8    Vaalers Thinglag                           Vaalers Thinglag
5    Rygge Thinglag                 Ryggihof    Rygge (Ryggihof) Thinglag
60   Skjebergs Thinglag             Skjaberg    Skjebergs (Skjaberg) Thinglag
108  Spydebergs Thinglag            Spjoteberg  Spydebergs (Spojteberg) Thinglag
220  Skedsmo og Nittedals Thinglag              Skedsmo og Nittedals Thinglag

What I’d like to do

I need to identify the first part of the name in col. E, add what’s in col. F (if anything) in parentheses, and then add what’s left from col. E. Some form of left function seems appropriate, but I do not know how to identify the end of the word when not counting number of symbols.

It could be that a right function is more appropriate, as exemplified by row 220; there potentially are areas that some time before this document was created were joined to form one governmental areas; the separator, as can be seen, would be ‘ Thinglag’ (with the space). In other words, the first part of cell 1 (E2:E) could contain more than one word which needs to be transposed. Using row 220 above as an example, if there was an entry in F220 (let’s say ‘Skeidsmo og Nøttedal’), G220 should read ‘Skedsmo og Nittedals (Skeidsmo og Nøttedal) Thinglag’.

An example formula to be inserted into F2:

=CONCATENATE(LEFT[word in E2];" (";F2;") "; [the rest of E2])

Answer 1

Try this:

IF(F2="",E2,CONCATENATE(LEFT(E2,SEARCH(" T",E2))&"("&F2&")"&MID(E2,SEARCH(" T",E2),Len(E2)-SEARCH(" T",E2)+1)))

It first checks that F2 is populated to see whether the strings need to be concatenated, then if they do it does the following:

1. Finds the position of the first space plus ‘T’ in E2 using SEARCH(" T",E2)
2. It uses this information to extract the first word in the string using LEFT(E2,SEARCH(" T",E2))
3. The first word is concatenated with the brackets and value of F2 using &"("&F2&")"&
4. The remaining words of the string in E2 are obtained using MID(E2,SEARCH(" T",E2),Len(E2)-SEARCH(" T",E2)+1) which locates the position of the first space plus ‘T’ in the string and gets the rest of the words that follow after it.