Replace a character in Excel Spreadsheet cell

I have a Parts List with over 2,000 part numbers - one part number per row and all in Column B.  The format of the part numbers is as follows:     9999-999-999

The software that I need to import the parts list into does not recognize the "-" (hyphen) embedded in the part number.  The part number format that MUST be adhered to in the new software requires a "space" where the "-" (hyphen) is now shown.

So, the newly formatted part number should look like this:      9999 999 999

Is there a way to make this replacement for all part numbers listed in the spreadsheet at one time?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi baleman2,

There sure is. Highlight/select column B, and press control-H to open the find/replace dialog. Enter a hyphen for the "find what", and a space for the "replace with" fields.

If you want it to only replace hyphens when the cell has a format of 9999-999-999, that can still be done but we'd have to write a macro for you to do it (easy enough). If needed, just let us know.

[ fanpages ]IT Services ConsultantCommented:
[edit] What Matt said! :) [/edit]


Select column [ B ], & then use the [CTRL]+[H] key combination to display the "Find and Replace" dialog box.

Enter a hyphen ("-" without the quotes) into the "Find what:" field.
Clear the "Replace with:" field if it contains anything already & enter a single space character (" ").

Click the [Options >>>] button & ensure that the following options are set:


"By Columns"

Look in:

[ ] Match case (not checked/ticked/selected)
[ ] Match entire cell contents (not checked/ticked/select)

Click the [Replace All] button.

That should be it!



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
baleman2Author Commented:
Thanks to you both.  I'm splitting the points equally because both solutions essentially gave me the same info and both were correct.
[ fanpages ]IT Services ConsultantCommented:
No problem at all.  I'm sure Matt won't mind.

Thanks in any respect & good luck with the rest of your project.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.