Identifying words by puntuation marks and deleting.

[CDandVinyl]

I'm trying to tidy a script by removing the names of the characters.

Each one in the text is followed directly by a colon or by a space and a colon.

So , the search would find colons preceded by text characters making single words.

It would find

John:
Sarah:
Steven:
Helen:

and delete each one.


It would also find

John :
Sarah :
Steven :
Helen :

and delete.


It wouldn't find colons with numerical characters or spaces on either side.

So it wouldn't find

21 : 21 : 00

or

21:21:00



Can someone advise a search and replace to satisfy this?

Grateful for any help...

[oeldere]

B1 =if(or(isnumber(mid(a1,search(":",a1,1)-1,1)*1)=true,isnumber(mid(a1,search(":",a1,1)-2,1)*1)=true),a1,left(a1,search(":",a1,1)-1)) and drag down.

[macropod]

oeldere: And how will that work in Word? It is, after all, a Word question...

[macropod]

You could do that with a wildcard Find/Replace, where:
Find = <[A-Za-z]@[a-z ]:
Replace = nothing

[oeldere]

@ macropod

The footer says.

Using XL2003 - Feb 1st 2020

[macropod]

Sig lines are irrelevant. It's posted in Word Formatting & General.

[CDandVinyl]

Thanks for this - it's working perfectly first time.

[macropod]

The wildcard Find/Replace expression I posted finds all those instances and deletes the word preceding the colon.

[CDandVinyl]

- sorry guys if my signature confused the issue. I'll amend it to avoid any future consternation.

[CDandVinyl]

The wildcard Find/Replace expression I posted finds all those instances and deletes the word preceding the colon.

Many thanks again for your time and expertise.

Problem Solved!

[CDandVinyl]

Just a FYI ;

I'm finding that where the name is in all capital letters followed immediately by a colon , the search/replace misses them.

Names with all other combination of upper / lower cases and spaces followed by a colon are found.

[macropod]

I'm finding that where the name is in all capital letters followed immediately by a colon , the search/replace misses them.

Without wanting to place too fine a point of it, none of your examples depicted that. Nevertheless, to handle that as well, you could use:
Find = <[A-Za-z]@[A-Za-z ]:

[CDandVinyl]

You're quite right - apologies. My oversight.

Your amended code covers all the bases and works perfectly. It's very interesting to work through the logic.

Thanks again.

[CDandVinyl]

As feedback - I'm running the Regex code suggested

Please Login or Register  to view this content.

and it's working brilliantly to remove the names in my scripts based upon the position of the colon. Whether the name is capital or lower case , whatever the position of spaces or not around the colon , it works.

In some few circumstances though , where a colon exists elsewhere in the text , it has some unintended consequences.

For example :

MARIA : But hark ye :what cunning match have you made.

Antony: Only this: let each man do his best.


Should become - with just the names at the start deleted :


But hark ye :what cunning match have you made.

Only this: let each man do his best.


Unfortunately , the code can't pick out specifically the colons with names preceding them. It is finding both colons and rendering these examples as

But hark what cunning match have you made.

Only let each man do his best.


Perhaps I should have thought of this in the initial post.

I was wondering , as each name is always only at the beginning of a line with no text or spaces preceding it , if there is a way to differentiate it from the other colons in the middle of a phrase which do have text and spaces preceding them.

The code would not have effect on numbers , and should search irrespective of upper and lower case , and absence or not of spaces.

Grateful for any advice.

[macropod]

You could try:
Find = <[A-Z][A-Za-z]@[A-Za-z ]:
Do note, however, this will not find colons preceded by a string of less than three characters (including the space).

[CDandVinyl]

Ok - thanks again. That's fixed it.