Thread: replace question

Hello Everyone --

This oughta be easy. In a list of names I have a column of middle initials that do not have periods. I want to add periods. I can get the periods all right, but everything I've tried wipes out the initial. So I need some kind of wildcard character that will leave whatever is already there alone.

Any ideas?

Thanks

Wick

Does the column only contain 1 character representing the middle initial or is it a full name with an initial in it?

Can you post an example or two?

Originally Posted by Wick

Hello Everyone --

This oughta be easy. In a list of names I have a column of middle initials that do not have periods. I want to add periods. I can get the periods all right, but everything I've tried wipes out the initial. So I need some kind of wildcard character that will leave whatever is already there alone.

Any ideas?

Thanks

Wick

Assuming your initials are in column A, use a helper column and in row 1 put

=A1&"."

and formula fill that down the column as required.
Copy this column and Paste Special = Values back over the A column, then delete the helper column.

hth
---

Hi --

I didn't understand what Bryan meant by "formula fill down the column."

What I have is a column with some empty cells and other cells containing a single random letter, e.g., "K". I want Excel to go down the column and wherever it finds a non-empty cell to add a period in position 2, giving, e.g., "K.".

I have tried using =REPLACE(?,2,1,".") but Excel objects to the "?" wildcard.

Thanks for any help

Wick

Originally Posted by Wick

Hi --

I didn't understand what Bryan meant by "formula fill down the column."

What I have is a column with some empty cells and other cells containing a single random letter, e.g., "K". I want Excel to go down the column and wherever it finds a non-empty cell to add a period in position 2, giving, e.g., "K.".

I have tried using =REPLACE(?,2,1,".") but Excel objects to the "?" wildcard.

Thanks for any help

Wick

enter a formula in a cell, in this case you have data in column A, therefore the assumption is column B, you can use a different column as per your requirements.

for data in A1 to A~~~~ and using column B as the helper column:

in B1 put

=A1&"."

press enter, then select that cell (click on it) to produce the outline box, this box has a small + in the bottom right corner, select that + (click-and drag) and 'drag' downwards.

In B2 should then be =A2&"." and in B3 =A3&"." etc etc

That is the simple form of 'formula-fill'

------------------------
There are other methods, you can copy B1 then select B2:B~~~ and Paste Special = formula

You can use the Formula Painter.

You can select B1:B~~~ with B1 as the active cell and (first) enter the formula

You can 'select' a range either by click-and-drag the required cells, or by typing the range B1:B~~~ into the Name box and press Enter.
------------------------

The Contextures site will give firther help on these (and many other) ideas if required.

---