How to ignore a hyphen within a cell

[Doggerry]

Hi

I wonder if anyone could give me a bit of advice on a formula please.

I am using the formula below to ensure that certain surnames have a space between them, however it is also adding a space after the hyphen in double barrelled surnames.

I am trying to put a space between Van and Kempen.

The formula I am using is - =REPLACE(B2, 1+MIN(FIND(CHAR(64+COLUMN($B$2:$Z$2)),MID(B2,2,255)&(CHAR(64+COLUMN($B$2:$Z$2))))), 0, " ")

Thank you in anticipation.

 

[Fluff]

How about

=IF(ISERROR(FIND("-",B2)),REPLACE(B2, 1+MIN(FIND(CHAR(64+COLUMN($B$2:$Z$2)),MID(B2,2,255)&(CHAR(64+COLUMN($B$2:$Z$2))))), 0, " "),B2)

 

[steve the fish]

Presuming your formula works as intended you could wrap a SUBSTITUTE formula around it:

=SUBSTITUTE(REPLACE(B2,1+MIN(FIND(CHAR(64+COLUMN($B$2:$Z$2)),MID(B2,2,255)&(CHAR(64+COLUMN($B$2:$Z$2))))),0," "),"- ","-")

 

[Doggerry]

Hi Fluff and Steve the Fish.

Thank you very much for your help, both work but will not pick up one name that is DeAsis, it should be De Asis. I have been trying to figure it out but with no joy. Any ideas?

Thanks.

 

[steve the fish]

It's because char(66) is B so you are missing As. Change the $B$ to $A$ in both column functions.

 

[Doggerry]

It's because char(66) is B so you are missing As. Change the $B$ to $A$ in both column functions.

Thank you very much, I will be able to sleep tonight now

 

[Peter_SSs]

I will be able to sleep tonight now

I hope that you do not have nightmares about names like these.

22 02 08.xlsm
	A	B	C	D
1	FNAME	SNAME	OUTPUT (Fluff)	OUTPUT (stf)
2	Jane	McDonald	Mc Donald	Mc Donald
3	Basil	D'Olivera	D' Olivera	D' Olivera
Sample

Cell Formulas
Range	Formula
C2:C3	C2	=IF(ISERROR(FIND("-",B2)),REPLACE(B2, 1+MIN(FIND(CHAR(64+COLUMN($A$2:$Z$2)),MID(B2,2,255)&(CHAR(64+COLUMN($A$2:$Z$2))))), 0, " "),B2)
D2:D3	D2	=SUBSTITUTE(REPLACE(B2,1+MIN(FIND(CHAR(64+COLUMN($A$2:$Z$2)),MID(B2,2,255)&(CHAR(64+COLUMN($A$2:$Z$2))))),0," "),"- ","-")

BTW, 'Mark as solution' is meant to mark the post that best suits you as the answer to your question. I have therefore removed the mark from post #6 as it does not meet that criteria.

I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)