Find and Replace Question

[Guest]

I have fullname column with first, middle initial, last name. Some of the names do not have middle initials.

Example
John W Meadows

I want to replace it with

John,W Meadows

I want to do nothing if it is
John Meadows
that is no middle initial

I know what to put in find box " ? " but I don't know what to put in replace.

Can it be done using Find/Replace or I have to do it some other way. Any help is greatly appreciated.

 

[Guest]

have you tried to hit the delete button or not put
anything? What works when I want to delete something is
I put {Find=DOS Replace= }
It's worth a try

-----Original Message-----
I have fullname column with first, middle initial, last

name. Some of the names do not have middle initials.

Example
John W Meadows

I want to replace it with

John,W Meadows

I want to do nothing if it is
John Meadows
that is no middle initial

I know what to put in find box " ? " but I don't know what to put in replace.

Can it be done using Find/Replace or I have to do it

some other way. Any help is greatly appreciated.

 

[Dave Peterson]

I think I'd use a helper cell with a formula:

=SUBSTITUTE(A1," ",",",1)

If you had:
John W T Meadows
you'd get:
John,W T Meadows

Are you sure that this is the format your data should be?

 

[Guest]

Thanks Dave for your response. I don't know what a helper cell is. Can you please tell me the steps to do it. I am new to excel. Sorry if it is a very basic question.

I always have one middle initial. I don't want to do anything if the fullname has no middle initial.

 

[Dave Peterson]

Say your data is in column A (A1:A999???).

then insert a new column right next to it--say column B. Use that column to
Help reformat your data.

So put:
=SUBSTITUTE(A1," ",",",1)
in B1

And use the autofill little thingy on the bottom right corner of that active
cell and drag it down (as far as you need.)

If you want to toss the original data and just keep the results of the formula,
you can select column B
Edit|copy
Edit|paste special|Values
and delete column A.

Do this junk against a copy of your workbook (or don't save it if it gets
screwed up!).

 

[Guest]

-----Original Message-----
I have fullname column with first, middle initial, last

name. Some of the names do not have middle initials.

Example
John W Meadows

I want to replace it with

John,W Meadows

I want to do nothing if it is
John Meadows
that is no middle initial

I know what to put in find box " ? " but I don't know what to put in replace.

Can it be done using Find/Replace or I have to do it

some other way. Any help is greatly appreciated.

 

[ElsiePOA]

Hi-

I think the problem with Dave's solution is that it puts a comma afte
every first name regrdless of whether there is a middle initial o
not.

I have a solution. It's involved, but if you have a large number o
names it may be worth your while.

First, assuming the names are in column A, insert 5 new columns to th
right. These can be deleted later. In column B, enter the numbers 1 t
x (x = the total number of names). This will allow you to sort the
back in their original order at the end.
Then select column A and use DATA, TEXT TO COLUMNS .
Select DELIMITES BY SPACE, and select cell $C$1. This will give yo
the first name, middle initial, and last name in columns C,D,&E.
Highlght these three columns and select copy and paste special
values.

Next, select columns B thru E and do a data sort using column E to sor
by. This will separate those names with a middle initial from thos
without one.

In cell F1, enter the formula =C1&", "&D1&" "&E1. Copy this down al
the way to the end of the names with the middle initial.

In the cell in Column F next to the name with no first initial (assum
F355) enter the fromula =C355&" "&D355&" "&E355. Copy this down to th
end of your names.

Now, select Column F and select copy. Select cell A1 and PAST
SPECIAL, VALUES. Select columns A & B and sort using column B.

You will now have the names in column A formatted as you want them i
back into their original order. You can then select columns B thru
and delete them.

I suggest you try this on a sample sheet with only two or thre names o
each type, so that you can see how it works.

There may be an easier solution, but I'n not sufficiently experience
to tell you what it is.

Good luck

 

[Dave Peterson]

Oops,

Here's one that gets rid of any extra spaces and then counts the number of
spaces left. If it's greater than 1, then it does the substitution, if not, it
just leaves it alone:

=IF(LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))=1,A1,
SUBSTITUTE(TRIM(A1)," ",",",1))

(all one cell).

 

[Dave Peterson]

If you drag the formula down, then A1 should change to A2, to A3....

That way the formula in Row 17 will refer to the value in A17.

I assumed your data was in one column (and you'd drag down).

If it's not that way, how's it laid out?

 

[Guest]

Thanks. I got it. My data is laid in one col only.

 

[Guest]

Great, that worked for me. Thanks so much.

Oops,

Here's one that gets rid of any extra spaces and then counts the number of
spaces left. If it's greater than 1, then it does the substitution, if not, it
just leaves it alone:

=IF(LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))=1,A1,
SUBSTITUTE(TRIM(A1)," ",",",1))

(all one cell).

 

[Guest]

Thanks...that's another way of doing it. Dave suggested an easier way, that worked for me.

 

[Harlan Grove]

Here's one that gets rid of any extra spaces and then counts the number of
spaces left. If it's greater than 1, then it does the substitution, if not, it
just leaves it alone:

=IF(LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))=1,A1,
SUBSTITUTE(TRIM(A1)," ",",",1))

(all one cell).

...

An alternative,

=SUBSTITUTE(TRIM(A1)," ",IF(ISNUMBER(SEARCH("?* ?* ?*",TRIM(A1))),","," "),1)