Eliminate spaces between Initials in First Names column 2

[johnbarr44]

I am checking files of transcribed data for differences.

I am finding many errors in the "First Names" column, which consists of a mixture of names and initials.

The problem is some transcribers have entered a space between two initials (sometimes three) and others have not. I would like to remove the spaces between initials, but not between entries of more than one name.

Sample entries:
John
John and sons
John and Fred
J
JF
J F
J F M

In above examples, I would like to remove spaces from the last two entries.

Thanks
John B

 

[macropod]

Assuming MS Word (you haven't said what application you're using), you could use a wildcard Find/Replace, where:
Find = ([A-Z]) ([A-Z][!a-z])
Replace = \1\2
You might need to execute it twice.

Cheers
Paul Edstein
[MS MVP - Word]

 

[johnbarr44]

Thanks for reply macropod,
sorry, it is all in Excel.

John B

 

[macropod]

Probably the quickest way is to copy & paste the data into Word, do the Find/Replace, then copy & paste the corrected data back into Excel.

Cheers
Paul Edstein
[MS MVP - Word]

 

[johnbarr44]

Paul, I tried that on a small portion, but without success.

First, I should have mentioned I'm using Office 2003.
Next, you mention a WILDCARD Find/Replace. Is that different to a standard Find/Replace?

and should I enter your whole formula e.g.(Find = ([A-Z]) ([A-Z][!a-z])) into the 'Find' space, or just the part after the = sign? and same for replace.

Thanks
John B

 

[macropod]

You need to check the 'use wildcards' option (you might need to click 'More' to see it). The Find/Replace expressions are used without the '=' sign.

Cheers
Paul Edstein
[MS MVP - Word]

 

[johnbarr44]

OK, I found the 'Use Wildcard' option, and have it checked.

Now, it is finding the space between the two words at top of column in first cell = "First Names", and eliminating that when I click 'replace', but then not finding any spaces in following cells of the table. ??

i.e. in example below:
It is changing 'FIRST NAME" to 'FirstName' which is not required,
but not the 'J B' to be JB and the 'A G' to AG, which is required.

FIRST NAME
George
J B
Barnabas
A G
John

Thanks
John B

 

[johnbarr44]

Sorry, 'FirstName' should be 'FIRSTNAME'

John B

 

[macropod]

How about using 'Replace All'??!!

Cheers
Paul Edstein
[MS MVP - Word]

 

[macropod]

To handle the UPPER CASE situation, change the Find expression to:
([A-Z]) ([A-Z][!a-zA-Z])

Cheers
Paul Edstein
[MS MVP - Word]

 

[johnbarr44]

I really appreciate your help, Paul, but still not getting a result.

I'm just getting the message "Word has completed its search of the document and has made 0 replacements"

Picture of my trial table attached.

John B

 

[macropod]

The table being created by your paste operation appears to be causing problems. It'll work fine if you paste into Word, via 'Paste Special' as 'Unformatted Text'.

Cheers
Paul Edstein
[MS MVP - Word]

 

[johnbarr44]

Yes!!! That worked OK. Great !

I have literally thousands of rows to check in several spreadsheets, so that will save me a lot.

It missed one case (rare) where there were three initials, H J B and just converted that to HJ B.

Can it be adjusted to handle that situation as well?

John B

 

[macropod]

As I said previously, you'll need to execute the Find/Replace twice.

Cheers
Paul Edstein
[MS MVP - Word]

 

[johnbarr44]

Ouch!! Yes, so you did. And that worked a treat.

Many Thanks
Cheers
John B

 

[Andrzejek]

One (not very sophisticated) way to do it in Excel would be a simple macro:

Sub Macro1()
Dim i As Integer

For i = 2 To 10
    If Len(Range("A" & i).Value) < 6 And _
        InStr(Range("A" & i).Value, " ") Then
        
        Range("A" & i).Value = Replace(Range("A" & i).Value, " ", "")
    End If
Next i

End Sub

Assuming FirstName is in column A, and data is in rows 2 to 10

Have fun.

---- Andy

 

[johnbarr44]

Thank you Andy,

That is much quicker and easier and does just what I wanted.

Cheers
John B