Insert a name at the end or within text

[Aidworker17]

Dear All,

Thanks to a brilliant solution from Bosco (to a previous problem), I have learned much of what I need to solve the following problem. However, I just need a final bit of assistance.

Column "Y" contains a city name followed by a client number. Then either nothing at the end, or the words "Not Captured". For example :

<CityNameAaaa>00001
<CityNameAaaa>00002 Not Captured

Further down the column, the city name changes, the numbering restarts, but there are still only the same 2 possible endings - either nothing, or the words "Not Captured". For example :

<CityNameBbbbbbbbbb>00001
<CityNameBbbbbbbbbb>00002 Not Captured

What I would like to do is take these cells and create cells that add the name of a beneficiary (column "J" is the surname and column "K" is the first name) - after the number. For example :

<CityNameAaaa>00001
would become
<CityNameAaaa>00001 Helen Jones

<CityNameAaaa>00002 Not Captured
would become
<CityNameAaaa>00002 Suzie Thomas Not Captured

Thanks to Bosco, I have worked out a formula that is close - as it looks for the space before the words "Not Captured" and then performs the magic :

=IF(Y9496="","",TRIM(LEFT(Y9496,FIND(" ",Y9496)-1) & " " & J9496 & " " & K9496 & " " & MID(Y9496,FIND(" ",Y9496)+1,99)))

However, there is no space after a number that does not have the words "Not Captured"; and so the formula crashes.

I was wondering if the search for the space could be changed to something like 'search for the first space OR an <end of cell character>'.

Alternatively, could the formula have an "IF" function whereby if there are the words "Not Captured" in the string, then add "Not Captured" to the end of the new string; otherwise don't add anything ?

Kind regards,
Jonathan.

 

[bosco_yip]

Try this ............

=IF(Y9496="","",IF(ISNUMBER(SEARCH("not captured",Y9496)),LEFT(Y9496,LEN(Y9496)-12)&J9496&" "&K9496&" Not Captured",Y9496&" "&J9496&" "&K9496))

or,

=IF(Y9496="","",REPLACE(Y9496,MATCH(1,INDEX(-MID(Y9496,ROW($1:$99),1),))+1,," "&J9496&" "&K9496))

Regards
Bosco

 

[Aidworker17]

Dear Bosco,

Champagne football once again.

The women of Northern Uganda thank you.

Regards,
Jonathan.