Replacing text or separators in selected Excel cells


Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

A little side-trick for converting text dates to Excel dates is replacing the text separator. Excel lets you replace text within the selected cells only.

If the text dates are in the correct order (e.g.  m/d/y ) but use the wrong separator for your Windows regional setting (e.g. m-d-y ).

Excel’s Replace function will replace text only in the current selection – but it’s not obvious. There’s no ‘Replace Selection’ option to choose.

Select the dates to change then open the Replace box (Home | Editing | Replace or better  Ctrl + H ).

replacing text or separators in selected excel cells microsoft excel 24514 - Replacing text or separators in selected Excel cells

Find what:  the character to find. In this case a backslash /

Replace with: the character to replace.  For this example, a dash –

You might expect to choose ‘Selection’ from the Within list, but no.

Since you already have selected cells, Replace All will only act on the selection.  See what we mean about not obvious?

replacing text or separators in selected excel cells microsoft excel 24515 - Replacing text or separators in selected Excel cells

subs profile e1563205311409 - Replacing text or separators in selected Excel cells
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address