Requesting help with a text formula

English
#1

Requesting help to create a new column text from two other columns.

Have a sheet with four columns, sample top two rows, in comma delimited, follows…

NUMBER,CALLSIGN,NAME,RESULT
3654,W1AEN,Alfred E Neumann,

In the fourth column I need help with a formula to generate the following result. The callsign followed by a space and only the first name. In all caps and surrounded with quotation marks. My sample would produce the following.

“W1AEN ALFRED”

Any help is greatly appreciated.

Thanks in advance,

#9
  1. Compound names are a mess in any case. In IT messed data are also a sin.
  2. To extract part of a compond name just to create a next compoud is a weird idea.
  3. See also this very substantial text about names.
    And even among your friends and important contacts may be a person named following a completely different concept (regional or national or language-specific) habit.

If you have data containing compound names, separate them first to a different sheet, split them into meaningful parts (if some are to find), check the results one by one, …
Then you may create different urgently needed compounds by concatenation (with TEXTJOIN() e.g.) easily elsewhere - and these may use the split parts in different order and context.

#2

Welcome to the minefield that is names - let me refer you to Falsehoods Programmers Believe About Names.

With that in mind, I’ll interpret your question as The callsign followed by a space and only the string of characters prior to the first space in the NAME field. Assuming your values are in row 2 starting at column A, and the space is a normal space character then

 ="""" & B2 & " " & UPPER(LEFT(C2,SEARCH(" ",C2)-1)) & """"

should do what you want.

1 Like
#8

By way of demonstration of my suggestion:
callsign.ods (17.5 KB)

Changed upload as there was some un-needed rubbish in original file.

#3

Tested this formula in a small section of my sheet. Worked correctly in one case and used data from the wrong row in an other. Resulted with a #VALUE! in most of the others.

Maybe I applied the matrix formula incorrectly ?

Trying to export more detail to illustrate the result.

Thanks again,

#7

I guess you mean the answer given by @robleyd Please use comments (symbol is the speech bubble) not answers for your replies unless they answer your original question.
.
@robleyd already warned of lurking problems and stated: “characters prior to the first space”. The formula fails, if there is no space, so you need to give an alternative value in this case (not shown by your single example).
.
Why did you use a matrix-formula?

#4

#5

#6

These are not solutions. Please use the comment bubble to add comments.
Copy @robleyd formula into g2 add three to the column references to adjust for the extra three prior columns. Do not try to add extra rows. Press enter, you should see the concatenation.
Copy the cell.
Select the cells below in the same column and paste.

[Edit 13h later in front of computer]
Search will fail for single word name
In G2 enter
=IFERROR("""" & E2 & " " & UPPER(LEFT(F2,SEARCH(" ",F2)-1)) & """",""""&E2&" "&F2&"""")
and drag or copy down

It could possibly be made shorter.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.